package com.hongdee.crm.kpi.service.impl;

import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.kpi.dao.IKPIDao;
import com.hongdee.crm.kpi.service.IKPIService;
import com.sun.org.apache.xpath.internal.operations.Equals;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.*;

import static com.hongdee.crm.Constant.ONE_DAY_MIC_SECOND;
import static com.hongdee.crm.Constant.SA_ROLE_ID;
import static com.hongdee.crm.Constant.companyAndAreaAndBrand;

/**
 * Created by My on 2017/3/1.
 */
@Service
public class KPIServiceImpl implements IKPIService {
    @Autowired
    private IKPIDao ikpiDao;
    private List<String> deptList;

    /**
     * @param
     * @throws Exception
     * @desc 活动分页
     * @author 徐钦勇
     */
    @Override
    public Map ActivePage(String start, String limit, String name, String oid, Map data) throws Exception {
        StringBuffer sqlBuffer = new StringBuffer();
        List params = new ArrayList();
        sqlBuffer.append("select * from (SELECT ");
        sqlBuffer.append("ta.id,");
        sqlBuffer.append("CASE ta.status when '2' then  '未开始' when '1' then '进行中' when '0' then '已结束' end status, ");
        sqlBuffer.append("ta.name,");
        //发券成本=（工时成本+物料成本）*优惠券状态不等于-1
        sqlBuffer.append("ROUND((ta.degression * 1+ta.gscost * 1)* COUNT(IF (tc. STATUS <> '-1' ");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(end);
            }
        }
        sqlBuffer.append(", TRUE, NULL)),2)  AS issueCost,");
        //消费成本=（工时成本+物料成本）*优惠券状态等于2已使用
        sqlBuffer.append("ROUND((ta.degression * 1 + ta.gscost * 1) * COUNT(IF(tc.STATUS = '2'");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.use_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.use_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
            }
        }
        sqlBuffer.append(",TRUE, NULL)),2) AS usedCost,");
        sqlBuffer.append("ta.gift,ta.gscost,ta.cost,ta.degression, ");
        sqlBuffer.append("ta.start_date, ");
        sqlBuffer.append("ta.stop_date,tc.gongdan_id, ");
        sqlBuffer.append("CASE WHEN ta.stop_date<unix_timestamp()*1000 then '100%' WHEN ");
        sqlBuffer.append("ta.start_date>unix_timestamp()*1000 then '0%' ELSE ");
        sqlBuffer.append("concat(ROUND((unix_timestamp()*1000-ta.start_date)/(ta.stop_date-ta.start_date),2)*100,'%') END  ing,");
       /*原发券数issue改现 发放数issue=1已打印2已使用*/
        sqlBuffer.append("COUNT(if((tc.status='1' or tc.status='2')");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
            }
        }
        sqlBuffer.append(",true,null)) issue,");
        //剩余数
        sqlBuffer.append("COUNT(if(tc.status='1' and tc.youxiaoqi>=?");
        params.add(DateUtils.getNowTime());
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
            }
        }
        sqlBuffer.append(",true,null)) surplus,");
        //过期数量
        sqlBuffer.append("COUNT(if(tc.status='1' and tc.youxiaoqi<?");
        params.add(DateUtils.getNowTime());
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
            }
        }
        sqlBuffer.append(",true,null)) expire,");
        //未打印
        sqlBuffer.append("COUNT(if(tc.status='0'");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
            }
        }
        sqlBuffer.append(",true,null)) noprint,");
        //参与人数改名为创建数
        sqlBuffer.append("COUNT(if(tc.status<>'-1' ");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
            }
        }
        sqlBuffer.append(",true,null)) joincount,");
        //原已消费改名为使用数
        sqlBuffer.append("COUNT(if( tc.status='2' ");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.use_date >= ? ");
                params.add(begin);
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.use_date <= ? ");
                params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
            }
        }
        sqlBuffer.append(",true,null)) used ,ta.start_department, ")
                .append(" SUM( if( tco.balance_date/1000 BETWEEN unix_timestamp(now())-604800 and unix_timestamp(now()) ,tco.real_amount,0)) 'match' ");
        sqlBuffer.append("FROM t_crm_car_activity ta ")
                .append(" left join t_crm_car_coupon tc on ta.id = tc.activity_id  ")
                .append(" left join t_crm_car_workorder tco on tco.order_num = tc.gongdan_id and tco.company_id = ?");
        params.add(oid);
        sqlBuffer.append("WHERE 1=1 ");
        if (!name.isEmpty()) {
            sqlBuffer.append(" and ta.name like ? ");
            params.add("%" + name + "%");
        }
        if (!oid.isEmpty()) {
            sqlBuffer.append(" and ta.company_id = ? ");
            params.add(oid);
        }
        //发起部门
        if (data.get("startDept") != "" && !StringUtils.isEmpty(data.get("startDept"))) {
            sqlBuffer.append(" and ta.start_department = ? ");
            params.add(data.get("startDept"));
        }
        sqlBuffer.append(" GROUP BY ta.id  ORDER BY ta.start_date desc) as temp ");
        Map map = this.ikpiDao.page(sqlBuffer.toString(), params.toArray(), start, limit);
        return map;
    }

    private List<Map<String, Object>> getCompanys(String uid) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("select  id from t_bc_organ where o_p_id in(  " +
                "                 select id from t_bc_organ where o_p_id in( " +
                "                 select id from t_bc_organ where o_p_id='0' " +
                "                 ) ) and id in (");
        List<Object> params = new ArrayList<>();
        Map<String, String> roleDate = TokenUtil.getRoleDate(uid);
        for (int i = 0; i < roleDate.size(); i++) {
            sql.append("?,");
            params.add(roleDate.get(i));
        }
        sql.append("?");
        params.add(TokenUtil.getCompanyId(uid));
        sql.append(")");
        return this.ikpiDao.list(sql.toString(), params.toArray());
    }

    /**
     * 集团报表 活动分析
     *
     * @param uid
     * @param start
     * @param limit
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public Map ActivePageGroup(String uid, String start, String limit, Map data) throws Exception {
        //获取公司信息
        StringBuffer sqlBuffer = new StringBuffer();
        List params = new ArrayList();
        sqlBuffer.append("select ta.*,o.o_name as companyName from (SELECT ");
        sqlBuffer.append("ta.id,ta.company_id,");
        sqlBuffer.append("CASE ta.status when '2' then  '未开始' when '1' then '进行中' when '0' then '已结束' end status, ");
        sqlBuffer.append("ta.name,");
        //发券成本=（工时成本+物料成本）*优惠券状态不等于-1
        sqlBuffer.append("ROUND(((ta.degression *1)+ (ta.gscost *1))* COUNT(IF (tc. STATUS <> '-1'");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                Long dateStartL = DateUtils.dayToLongTime(begin);
                //params.add(begin);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                Long dateStartL = DateUtils.dayToLongTime(end);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
        }
        sqlBuffer.append(", TRUE, NULL)),2)  AS issueCost,");
        //消费成本=（工时成本+物料成本）*优惠券状态等于2已使用
        sqlBuffer.append("ROUND(((ta.degression *1)+ (ta.gscost *1))* COUNT(IF (tc. STATUS =  '2' ");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.use_date >= ? ");
                //params.add(begin);
                Long dateStartL = DateUtils.dayToLongTime(begin);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.use_date <= ? ");
                Long dateStartL = DateUtils.dayToLongTime(end);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
                //params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
            }
        }
        sqlBuffer.append(",TRUE,NULL)),2) AS usedCost,");
        sqlBuffer.append("ta.gift,ta.gscost,ta.cost,ta.degression, ");
        sqlBuffer.append("ta.start_date, ");
        sqlBuffer.append("ta.stop_date, ");
        sqlBuffer.append("CASE WHEN ta.stop_date<unix_timestamp()*1000 then '100%' WHEN ");
        sqlBuffer.append("ta.start_date>unix_timestamp()*1000 then '0%' ELSE ");
        sqlBuffer.append("concat(ROUND((unix_timestamp()*1000-ta.start_date)/(ta.stop_date-ta.start_date),2)*100,'%') END  ing,");
       /*原发券数issue改现 发放数issue=1已打印2已使用*/
        sqlBuffer.append("COUNT(if((tc.status='1' or tc.status='2')");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                //params.add(begin);
                Long dateStartL = DateUtils.dayToLongTime(begin);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));

            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                //params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
                Long dateStartL = DateUtils.dayToLongTime(end);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
        }
        sqlBuffer.append(",true,null)) issue,");
        //剩余数
        sqlBuffer.append("COUNT(if(tc.status='1' and tc.youxiaoqi>=?");
        params.add(DateUtils.getNowTime());
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                //params.add(begin);
                Long dateStartL = DateUtils.dayToLongTime(begin);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                //params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
                Long dateStartL = DateUtils.dayToLongTime(end);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
        }
        sqlBuffer.append(",true,null)) surplus,");
        //过期数量
        sqlBuffer.append("COUNT(if(tc.status='1' and tc.youxiaoqi<?");
        params.add(DateUtils.getNowTime());
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                //params.add(begin);
                Long dateStartL = DateUtils.dayToLongTime(begin);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                //params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
                Long dateStartL = DateUtils.dayToLongTime(end);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
        }
        sqlBuffer.append(",true,null)) expire,");
        //未打印
        sqlBuffer.append("COUNT(if(tc.status='0'");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                //params.add(begin);
                Long dateStartL = DateUtils.dayToLongTime(begin);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                //params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
                Long dateStartL = DateUtils.dayToLongTime(end);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
        }
        sqlBuffer.append(",true,null)) noprint,");
        //参与人数改名为创建数
        sqlBuffer.append("COUNT(if(tc.status<>'-1' ");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.dayin_date >= ? ");
                //params.add(begin);
                Long dateStartL = DateUtils.dayToLongTime(begin);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.dayin_date <= ? ");
                //params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
                Long dateStartL = DateUtils.dayToLongTime(end);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
        }
        sqlBuffer.append(",true,null)) joincount,");
        //原已消费改名为使用数
        sqlBuffer.append("COUNT(if( tc.status='2' ");
        if (!data.isEmpty()) {
            if (!String.valueOf(data.get("start")).equals("") && data.get("start") != null && !String.valueOf(data.get("start")).equals("null")) {
                String begin = data.get("start").toString();
                sqlBuffer.append(" and tc.use_date >= ? ");
                //params.add(begin);
                Long dateStartL = DateUtils.dayToLongTime(begin);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
            if (!String.valueOf(data.get("stop")).equals("") && data.get("stop") != null && !String.valueOf(data.get("stop")).equals("null")) {
                String end = data.get("stop").toString();
                sqlBuffer.append(" and tc.use_date <= ? ");
                // params.add(DateUtils.addDayLongTime(Long.valueOf(end), +1));
                Long dateStartL = DateUtils.dayToLongTime(end);
                params.add(DateUtils.addDayLongTime(dateStartL, +1));
            }
        }
        sqlBuffer.append(",true,null)) used ,ta.start_department, ")
                .append(" SUM( if( tco.balance_date/1000 BETWEEN unix_timestamp(now())-604800 and unix_timestamp(now()) ,tco.real_amount,0)) 'match' ");
        sqlBuffer.append("FROM t_crm_car_activity ta ")
                .append(" left join t_crm_car_coupon tc on ta.id = tc.activity_id  ")
                .append(" left join t_crm_car_workorder tco on tco.order_num = tc.gongdan_id and tco.company_id = tc.company_id ");
        sqlBuffer.append(" GROUP BY ta.id  ORDER BY ta.start_date desc) as ta,t_bc_organ o  ");
        sqlBuffer.append("WHERE ta.company_id=o.id ");
        if (!"".equals(StringUtil.formatEmpty(data.get("name")))) {
            sqlBuffer.append(" and ta.name like ? ");
            params.add("%" + StringUtil.formatNull(data.get("name")) + "%");
        }
        Constant.companyAndAreaAndBrand(uid, StringUtil.formatEmpty(data.get("companyId")), StringUtil.formatEmpty(data.get("area")),
                StringUtil.formatEmpty(data.get("brand")), sqlBuffer, params, "o");
        /*if (!"".equals(StringUtil.formatEmpty(data.get("companyId")))) {
            sqlBuffer.append(" and ta.company_id = ? ");
            params.add(StringUtil.formatNull(data.get("companyId")));
        }else{
            sqlBuffer.append(" and ta.company_id in( ");
            for(int i=0;i<companyMaps.size();i++){
                sqlBuffer.append("?");
                Map infoMap=companyMaps.get(i);
                String id=String.valueOf(infoMap.get("id"));
                params.add(id);
                if(i<(companyMaps.size()-1)){
                    sqlBuffer.append(" , ");
                }
            }
            sqlBuffer.append(" ) ");
        }
        //区域
        if(!"".equals(StringUtil.formatEmpty(data.get("area")))){
            sqlBuffer.append(" and o.o_area=? ");
            params.add(StringUtil.formatEmpty(data.get("area")));
        }
        //品牌
        if(!"".equals(StringUtil.formatEmpty(data.get("brand")))){
            sqlBuffer.append(" and o.o_car_brand=? ");
            params.add(StringUtil.formatEmpty(data.get("brand")));
        }*/
        //发起部门
        if (data.get("startDept") != "" && !StringUtils.isEmpty(data.get("startDept"))) {
            sqlBuffer.append(" and ta.start_department = ? ");
            params.add(data.get("startDept"));
        }
        Map map = this.ikpiDao.page(sqlBuffer.toString(), params.toArray(), start, limit);
        return map;
    }

    /**
     * @param
     * @return Map
     * @throws Exception
     * @desc 优惠卷详情
     * @author 徐钦勇
     */
    @Override
    public Map CouponPage(String uid, String start, String limit, String id, String type, String timeType, Map data) throws Exception {
        StringBuffer sqlBuffer = new StringBuffer();
        List params = new ArrayList();
        sqlBuffer.append("SELECT ");
        sqlBuffer.append("ta.gift,  ");
        sqlBuffer.append("ta.degression,             ");
        sqlBuffer.append("tc.num,");
        sqlBuffer.append("ta.start_date,             ");
        sqlBuffer.append("ta.stop_date,              ");
        sqlBuffer.append("tc.dayin_date,             ");
        sqlBuffer.append("tc.contact_name,           ");
        sqlBuffer.append("tc.contact_phone,          ");
        sqlBuffer.append("tc.plate_num,              ");
        sqlBuffer.append("tco.order_num gongdanId,   ");
        sqlBuffer.append("tc.use_date ,              ");
        sqlBuffer.append("ta.name ,ta.degression+ta.gscost as activityCost ");
        sqlBuffer.append("FROM t_crm_car_coupon tc   ");
        sqlBuffer.append("LEFT JOIN t_crm_car_activity ta on tc.activity_id=ta.id  ");
        sqlBuffer.append("left join t_crm_car_workorder tco ON tco.order_num = tc.gongdan_id and tco.company_id = ? ");
        sqlBuffer.append("WHERE  tc.status <> '-1' ");
        params.add(TokenUtil.getCompanyId(uid));
        if (id.isEmpty()) {
            throw new Exception("请选择活动！");
        } else {
            sqlBuffer.append(" and tc.activity_id = ? ");
            params.add(id);
        }
        if (!type.isEmpty()) {
            if (type.equals("2")) {
                sqlBuffer.append(" and tc.status = ? ");
                params.add(type);
            } else {
                sqlBuffer.append(" and tc.status != 2 ");
            }
        }
        if (!timeType.isEmpty() && timeType != null) {
            if (data != null && !data.isEmpty()) {
                switch (timeType) {
                    //打印时间
                    case "0": {
                        if (data.get("start") != null && !data.get("start").equals("")) {
                            String begin = data.get("start").toString();
                            sqlBuffer.append(" and tc.dayin_date >= ? ");
                            params.add(begin);
                        }
                        if (data.get("stop") != null && !data.get("stop").equals("")) {
                            String end = data.get("stop").toString();
                            sqlBuffer.append(" and tc.dayin_date <= ? ");
                            params.add(Long.valueOf(end) + ONE_DAY_MIC_SECOND);
                        }
                    }
                    break;
                    //使用时间
                    case "1": {
                        if (data.get("start") != null && !data.get("start").equals("")) {
                            String begin = data.get("start").toString();
                            sqlBuffer.append(" and tc.use_date >= ? ");
                            params.add(begin);
                        }
                        if (data.get("stop") != null && !data.get("stop").equals("")) {
                            String end = data.get("stop").toString();
                            sqlBuffer.append(" and tc.use_date <= ? ");
                            params.add(Long.valueOf(end) + ONE_DAY_MIC_SECOND);
                        }
                    }
                    break;
                    default:
                        throw new Exception("请选择已有时间类型！");
                }
            }
        }
        Map map = this.ikpiDao.page(sqlBuffer.toString(), params.toArray(), start, limit);
        return map;
    }

    /**
     * 活动分布报表
     * 作者：徐钦勇
     */
    @Override
    public Map activityTable(String activityId) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List pamams = new ArrayList();
        sqlBuf.append("SELECT temp.activity_id, temp.start_date+28800000 start_date, ");
        sqlBuf.append("temp.stop_date, temp.create_date, ");
        sqlBuf.append("COUNT(IF(temp.STATUS != '-1', TRUE, NULL)) AS people, ");
        sqlBuf.append("count(IF (temp. STATUS = '2', TRUE, NULL)) AS used ");
        sqlBuf.append("FROM ( SELECT ");
        sqlBuf.append("a.start_date  AS start_date, ");
        sqlBuf.append("a.stop_date, c.activity_id, c.num, ");
        sqlBuf.append("c.create_date - (c.create_date %(86400000)+28800000) AS create_date, ");
        sqlBuf.append("c.dayin_date - (c.dayin_date %(86400000)+28800000) AS dayin_date,   ");
        sqlBuf.append("c.use_date - (c.use_date %(86400000)+28800000) AS use_date,         ");
        sqlBuf.append("c. STATUS FROM t_crm_car_coupon c, t_crm_car_activity a    ");
        sqlBuf.append("WHERE a.id = c.activity_id AND c.activity_id = ? AND c.status<>'-1' ) temp GROUP BY temp.activity_id, ");
        sqlBuf.append("temp.start_date, temp.stop_date, temp.create_date");
        pamams.add(activityId);
        List<List> usedTotal = new ArrayList();
        List<List> peopeleTotal = new ArrayList();
        List usedSumTotal = new ArrayList<>();
        List peopleSumTotal = new ArrayList<>();
        Map temp = new HashMap();
        Map tempSum = new HashMap();
        List<Map<String, Object>> list = this.ikpiDao.list(sqlBuf.toString(), pamams.toArray());

        int usedSum = 0;
        int peopleSum = 0;

        for (Map map : list) {
            List usedTemp = new ArrayList();
            List sumTemp = new ArrayList();
            List sumUsed = new ArrayList();
            List sumPeople = new ArrayList();

            usedTemp.add(map.get("createDate"));
            usedTemp.add(map.get("used"));
            sumTemp.add(map.get("createDate"));
            sumTemp.add(map.get("people"));

            sumUsed.add(map.get("createDate"));
            sumUsed.add(usedSum = usedSum + Integer.parseInt(String.valueOf(map.get("used"))));
            sumPeople.add(map.get("createDate"));
            sumPeople.add(peopleSum = peopleSum + Integer.parseInt(String.valueOf(map.get("people"))));

            usedTotal.add(usedTemp);
            peopeleTotal.add(sumTemp);

            usedSumTotal.add(sumUsed);
            peopleSumTotal.add(sumPeople);

            temp.put("start", map.get("startDate"));
            temp.put("stop", map.get("stopDate"));
        }

        temp.put("people", peopeleTotal);
        temp.put("used", usedTotal);
        temp.put("sumUsed", usedSumTotal);
        temp.put("sumPeople", peopleSumTotal);


        return temp;
    }

    /**
     * sa过程管理报表
     * 作者：徐钦勇
     */
    @Override
    public Map SaProcess(String uid, String start, String limit, String mDate, String area, String brand, String companyId) throws Exception {
        try {

            StringBuffer sqlBuf = new StringBuffer();
            List params = new ArrayList();
            Map map = new HashMap();
//            String companyId = TokenUtil.getCompanyId(uid);
            //取 companyId下的所有部门ID
            deptList = new ArrayList<>();
            deptList.add(companyId);
            buildDeptChild(companyId);
            if (null == mDate || mDate.length() == 0) {
                SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM");
                mDate = sf.format(new Date());
            }
            /**Fixed 01**/
            //sqlBuf.append("SELECT * FROM ( SELECT tu.u_name, ");
            /**Fixed 01*/
            /**
             * 调整：人员主表改为从表，增加公司ID过滤
             * */
            sqlBuf.append("SELECT * FROM ( SELECT tr.sa_id,if((tr.sa_id is null or tr.sa_id=''),'未分配',if(tr.u_name is null,'已离职',tr.u_name)) as u_name, ");
            /**Fiexed 02**/

            // 今日所有任务 提醒日期在今天与今天之前未完成的，提醒日期在今天与今天之前，但是今天处理的
            //2017-08-11今日所有任务去除过期任务今日完成的任务
            sqlBuf.append("COUNT(IF((tr.expect_date <= DATE_FORMAT(NOW(), '%Y-%m-%d') " +
                    "                AND tr.status = '0') " +
//                    "                OR (expect_date = DATE_FORMAT(NOW(), '%Y-%m-%d')) " +
                    //  "                OR (((expect_date < DATE_FORMAT(NOW(), '%Y-%m-%d')) " +
                    //  "                AND tr.status <> '0' " +
                    //  "                AND (tr.deal_date = DATE_FORMAT(NOW(), '%Y-%m-%d'))))"+
                    ",TRUE,NULL)) totalToday, ");
            // 今日已完成任务
            sqlBuf.append("COUNT(IF(tr.deal_date = date_format(now(),'%Y-%m-%d') AND tr.status = '1',TRUE,NULL)) doneToday, ");
            // 今日已关闭任务
            sqlBuf.append("COUNT(IF(tr.deal_date = date_format(now(),'%Y-%m-%d') AND tr.status = '2',TRUE,NULL)) closeToday, ");
            // 本月保险所有任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='bx',TRUE,NULL)) bx, ");
            // 本月保养所有任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='by',TRUE,NULL)) baoy, ");
            // 本月流失所有任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='ls',TRUE,NULL)) ls, ");
            // 本月关爱所有任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='ga',TRUE,NULL)) ga, ");
            // 本月美容所有任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='mr',TRUE,NULL)) mr, ");
            // 本月卡所有任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='k',TRUE,NULL)) k, ");
            // 本月自定义所有任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='qt',TRUE,NULL)) zdy, ");
            // 本月所有任务总计
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "',TRUE,NULL)) totalMon, ");
            //2017-08-1已完成加已关闭的任务
            // 本月保险已完成任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='bx' AND tr.status IN ('1','2'),TRUE,NULL)) doneBx, ");
            // 本月保养已完成任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='by' AND tr.status IN ('1','2'),TRUE,NULL)) doneBy, ");
            // 本月流失已完成任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='ls' AND tr.status IN ('1','2'),TRUE,NULL)) doneLs, ");
            // 本月关爱已完成任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='ga' AND tr.status IN ('1','2'),TRUE,NULL)) doneGa, ");
            // 本月美容已完成任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='mr' AND tr.status IN ('1','2'),TRUE,NULL)) doneMr, ");
            // 本月卡已完成任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='k' AND tr.status IN ('1','2'),TRUE,NULL)) doneK, ");
            // 本月自定义已完成任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.type_pcode='qt' AND tr.status IN ('1','2'),TRUE,NULL)) doneZdy,");
            // 本月关闭任务
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.status='2',TRUE,NULL)) close, ");
            // 本月已完成任务总计
            sqlBuf.append("COUNT(IF(left(tr.expect_date,7) = '" + mDate + "' AND tr.status IN ('1','2'),TRUE,NULL)) doneTotalMon, ");
            // 过期任务 为未完成的任务
            sqlBuf.append("COUNT(IF( tr.expect_date < date_format(now(),'%Y-%m-%d') and tr.status = '0' ,TRUE,NULL)) overdue,bc.o_name,tr.company_id ");

            /**fixed02**/
            sqlBuf.append("from                 ");
            sqlBuf.append(" (select tr.*,tu.u_name,tu.o_id from t_crm_task_real tr LEFT JOIN t_bc_user tu on tr.sa_id = tu.id ) tr ");
            sqlBuf.append(" , ");
            sqlBuf.append(" (select * from t_bc_organ ");

            //Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
            sqlBuf.append(" ) bc ");
            sqlBuf.append(" where tr.company_id='0' AND tr.o_id = bc.id and  DATE_FORMAT(tr.expect_date,'%Y-%m')= ? ");
            params.add(mDate);

            /*if (!TokenUtil.isCompanyAdmin(uid)) {
                sqlBuf.append(" and tr.sa_id = ? ");
                params.add(uid);
            }*/

            Map orgInfo = TokenUtil.getOrgInfo(uid);
            Map deptMap = TokenUtil.getDept(uid);
            boolean isStoreSa = false;
            String orgStoreId = "";
            if(deptMap != null){
                if(deptMap.get("oPId").equals("8a9aa28460026f310160059c8dd804b2")){ // 属于门店角色
                    orgStoreId = (String) orgInfo.get("deptId");
                    isStoreSa = true;
                }
            }
            if(!isStoreSa){ // 如果不是门店及以下权限，则都显示
                if(deptMap.get("oPId").equals("401e8482626facfb01626fbf26ee0624")){ // 属于区域角色
                    //"oAreaSecond" -> "JNBBQY"
                    String  oIdList = this.getAllOrganByArea(deptMap.get("oAreaSecond").toString());
                    sqlBuf.append(" AND tr.o_id IN ( "+oIdList+" )");
                }
            }
            else { // 如果是门店sa，则只显示此门店下的
                sqlBuf.append(" AND tr.o_id  = ? ");
                params.add(orgStoreId);
            }




            sqlBuf.append(" group by tr.sa_id,tr.company_id ) a ");
            /**fixed02**/
            Map taskMap = this.ikpiDao.page(sqlBuf.toString(), params.toArray(), start, limit);
            //维系记录统计
            StringBuffer sqlcontact = new StringBuffer();
            List param = new ArrayList();

            sqlcontact.append("SELECT * FROM (SELECT tc.u_name, ");

            sqlcontact.append("COUNT(IF(FROM_UNIXTIME(tc.create_time/1000,'%Y-%m-%d')=date_format(NOW(),'%Y-%m-%d'),TRUE,NULL)) contactToday, ");
            sqlcontact.append("COUNT(IF(FROM_UNIXTIME(tc.create_time/1000,'%Y-%m-%d')=date_format(NOW(),'%Y-%m-%d') AND tc.status='1',TRUE,NULL)) telToday, ");
            sqlcontact.append("COUNT(IF(FROM_UNIXTIME(tc.create_time/1000,'%Y-%m-%d')=date_format(NOW(),'%Y-%m-%d') AND tc.new_remind='1',TRUE,NULL)) yy, ");
            sqlcontact.append("COUNT(IF(FROM_UNIXTIME(tc.create_time/1000,'%Y-%m-%d')=date_sub(curdate(),interval 1 day),TRUE,NULL)) contactYes, ");
            //电话
            sqlcontact.append("COUNT(IF(FROM_UNIXTIME(tc.create_time/1000,'%Y-%m-%d')=date_sub(curdate(),interval 1 day) AND tc.status='1',TRUE,NULL)) telYes, ");
            sqlcontact.append("COUNT(IF(left(FROM_UNIXTIME(tc.create_time/1000,'%Y-%m'),7)= '" + mDate + "' ,TRUE,NULL)) contactMon, ");
            //本月电话
            sqlcontact.append("COUNT(IF(left(FROM_UNIXTIME(tc.create_time/1000,'%Y-%m'),7)='" + mDate + "' AND tc.status='1',TRUE,NULL)) telMon," +
                    "bc.o_name,tc.company_id ");
            /**fixed01**/
            /**fixed02**/
            sqlcontact.append("FROM (select tc.*,tu.u_name from t_crm_car_contact tc   LEFT JOIN t_bc_user  tu ON tu.id = tc.sa_id) tc");
            sqlcontact.append(",");
            sqlcontact.append(" (select * from t_bc_organ ");
            //Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlcontact, param, "");
            sqlcontact.append(" )bc ");
            sqlcontact.append(" WHERE tc.company_id='0' AND bc.id = tc.store_id and FROM_UNIXTIME(tc.CREATE_TIME/1000,'%Y-%m')  = ?  ");


            param.add(mDate);
            if (!TokenUtil.isCompanyAdmin(uid)) {
                sqlBuf.append(" and tc.create_user = ? ");
                params.add(uid);
            }
            sqlcontact.append(" GROUP BY tc.sa_id,tc.company_id  ) AS b");
            /**fixed02**/
            Map contactMap = this.ikpiDao.page(sqlcontact.toString(), param.toArray(), start, limit);
            //根据sa拼接任务和维系记录统计
            map.put("total", taskMap.get("total"));
            List<Map> tempTaskList = (List) taskMap.get("dataList");
            List<Map> tempContactList = (List) contactMap.get("dataList");
            List totalList = new ArrayList();
            for (Map tempTaskMap : tempTaskList) {
                Map dataMap = tempTaskMap;
                for (Map tempContactMap : tempContactList) {
                    if (tempTaskMap.get("uName").toString().equals(tempContactMap.get("uName").toString())) {
                        dataMap.putAll(tempContactMap);
                    }
                }
                totalList.add(dataMap);
            }
            map.put("dataList", totalList);
            return map;
        } catch (Exception e) {
            e.printStackTrace();
            return new HashMap();
        }
    }

    /**
     * 获取区域下所有门店
     * @param area
     * @return
     * @throws Exception
     */
    private String getAllOrganByArea(String area) throws Exception{
        String sql = "select * from t_bc_organ where o_area_second = ? ";
        List list = this.ikpiDao.list(sql,area);
        String oIdList = " '' ";
        for(Object o:list){
            oIdList += " ,'"+((Map)o).get("id")+"' ";
        }
        return oIdList;
    }

    /**
     * SA过程管理任务穿透明细
     *
     * @param uid
     * @param start
     * @param limit
     * @param status
     * @param pcode
     * @param code
     * @param type
     * @return
     * @throws Exception
     */
    @Override
    public Map SaProcessDetail(String uid, String saId, String start, String limit, String status, String pcode,
                               String code, String type, String mDate, String companyId) throws Exception {
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<>();
        sql.append(" select * from ( ");
        sql.append(" SELECT " +
                " crmtask.*,tuser.u_name,paramDef.name" +
                //",crmtask.task_nums as cp" +
                ",");
        sql.append("case when crmtask.type_code='zdy' then '自定义' else paramDef.name  end    typeName,");
        sql.append("CASE " +
                "WHEN crmtask.status='0' THEN '未完成' " +
                "WHEN crmtask.status='1' THEN '已完成' " +
                "WHEN crmtask.status='2' THEN '关闭' " +
                "ELSE '' END statusName ");
        sql.append(" FROM ");
        sql.append(" (select * from t_crm_task_real task ");
        sql.append(" where  task.company_id = ? ");
        params.add(companyId);
        //类型大类
        if (!StringUtils.isEmpty(pcode)) {
            if ("bx".equals(pcode)) {
                sql.append(" AND task.type_pcode like ?  ");
                params.add(pcode + "%");
            } else {
                sql.append(" AND task.type_pcode = ?  ");
                params.add(pcode);
            }
        }
        //类型小类
        if (!StringUtils.isEmpty(code)) {
            if ("bx".equals(pcode)) {
                sql.append(" AND task.type_pcode like  ?");
                params.add(code + "%");
            } else {
                sql.append(" AND task.type_code = ?");
                params.add(code);
            }
        }
        //状态分0，1，2，3，4其中0，1，2与数据库记录状态一致，3为延期状态，4为非延期状态
        if (!StringUtils.isEmpty(status)) {
            if ("1".equals(status)) {
                sql.append(" and task.status > 0 ");
            } else {
                sql.append(" and task.status = ? ");
                params.add(status);
            }
        }
        if ("day".equals(type)) {
            if ("1".equals(status) || "2".equals(status)) {
                sql.append(" and task.deal_date=DATE_FORMAT(NOW(),'%Y-%m-%d')  ");
            } else {
                sql.append(" and task.expect_date <= DATE_FORMAT(NOW(),'%Y-%m-%d') ");
            }
        }
        if ("month".equals(type)) {
            if ("".equals(mDate)) {
                sql.append(" and DATE_FORMAT(expect_date,'%Y%m')= DATE_FORMAT(now(),'%Y%m')");
            } else {
                sql.append(" and DATE_FORMAT(expect_date,'%Y%m')=DATE_FORMAT(?,'%Y%m') ");
                params.add(mDate + "-01");
            }
        }
        if ("before".equals(type)) {
            sql.append(" and task.expect_date < DATE_FORMAT(NOW(),'%Y-%m-%d') ");
        }
        if (!"".equals(saId)) {
            if ("未分配".equals(saId)) {
                sql.append(" and task.sa_id is null ");
            } else {
                sql.append(" and task.sa_id =? ");
                params.add(saId);
            }
        }
        sql.append(" ) crmtask " +
                " LEFT JOIN t_bc_user tuser ON crmtask.sa_id = tuser.id " +
                " LEFT JOIN t_bc_param_def paramDef ON crmtask.type_code=paramDef.code ");
        sql.append(" order by crmtask.expect_date asc,crmtask.type_pcode desc ");
        sql.append(" ) temp ");
        return this.ikpiDao.page(sql.toString(), params.toArray(), start, limit);
    }


    /**
     * SA过程管理维系穿透明细
     *
     * @param uid
     * @param mDate
     * @param type
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map SaProcessContactDetail(String uid, String mDate, String type, String status, String saId, String start, String limit,
                                      String companyId) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List<Object> params = new ArrayList<>();
        sqlBuf.append("SELECT      ");
        sqlBuf.append("cc.manitain_sender,     /*送修人*/        ");
        sqlBuf.append("cc.sender_tel,        /*送修人电话*/    ");
        sqlBuf.append("cc.model model,         /*车型*/          ");
        sqlBuf.append("ct.vin,                 /*vin*/           ");
        sqlBuf.append("ct.next_date,        /*持续跟进时间*/           ");
        sqlBuf.append("ct.next_content,        /*持续跟进内容*/           ");
        sqlBuf.append("cc.owner_name,          /*车主姓名*/      ");
        sqlBuf.append("tu.u_name,              /*客户经理*/      ");
        sqlBuf.append("ct.plate_num,           /*车牌号*/        ");
        sqlBuf.append("ct.record_path,           /*录音地址*/");
        sqlBuf.append("ct.title,               /*维系类别*/      ");
        sqlBuf.append("ct.content,			   /*维系内容*/      ");
        sqlBuf.append("ct.record_length,       /*通话时长*/      ");
        sqlBuf.append("ct.create_time,         /*时间*/          ");
        sqlBuf.append("ct.new_remind,          /*预约*/          ");
        sqlBuf.append("ct.sa_id,               /*saID*/          ");
        sqlBuf.append("ct.new_remind,          /*预约类型*/      ");
        sqlBuf.append("ct.parent_id,            /*维系大类*/      ");
        sqlBuf.append("cc.brand,               /*品牌*/          ");
        sqlBuf.append("cc.series,              /*车系*/          ");
        sqlBuf.append("cc.configure,           /*车型*/          ");
        sqlBuf.append("cc.last_maintain_date, /*进厂时间*/");
        sqlBuf.append("cc.maintain_start_mile,  /*维修起始里程*/");
        sqlBuf.append(" CASE " +
                "WHEN exd.visit_result ='success' THEN '成功' " +
                "WHEN exd.visit_result ='fail' THEN '失败' end as visit_result, ");
        sqlBuf.append(" CASE " +
                "WHEN exd.task_pcode = 'zh' THEN " +
                " '综合' " +
                "WHEN exd.task_pcode = 'by' THEN " +
                " '保养' " +
                "WHEN exd.task_pcode = 'bx' THEN " +
                " '保险' " +
                "WHEN exd.task_pcode = 'ls' THEN " +
                " '流失' " +
                "WHEN exd.task_pcode = 'ga' THEN " +
                " '关爱' " +
                "END AS task_pcode, " +
                " CASE " +
                "WHEN exd.task_code = 'sc' THEN " +
                " '手册' " +
                "WHEN exd.task_code = 'xbx' THEN " +
                " '新保续' " +
                "WHEN exd.task_code = 'xubx' THEN " +
                " '续保续' " +
                "WHEN exd.task_code = 'hy' THEN " +
                " '活跃' " +
                "WHEN exd.task_code = 'fhy' THEN " +
                " '非活跃' " +
                "WHEN exd.task_code = 'sr' THEN " +
                " '生日' " +
                "WHEN exd.task_code = 'ns' THEN " +
                " '年审' " +
                "WHEN exd.task_code = 'rc' THEN " +
                " '日常' " +
                "WHEN exd.task_code = 'yb' THEN " +
                " '延保' " +
                "WHEN exd.task_code = 'hd' THEN " +
                " '活动' " +
                "WHEN exd.task_code = 'srhf' THEN " +
                " '三日回访' " +
                "WHEN exd.task_code = 'zls' THEN " +
                " '准流失' " +
                "WHEN exd.task_code = 'yls' THEN " +
                " '流失' " +
                "WHEN exd.task_code = 'sb' THEN " +
                " '首保' " +
                "WHEN exd.task_code = 'sbm' THEN " +
                " '目标月首保' " +
                "WHEN exd.task_code = 'eb' THEN " +
                " '二保' " +
                "WHEN exd.task_code = 'ebm' THEN " +
                " '目标月二保' " +
                "WHEN exd.task_code = 'db' THEN " +
                " '定保' " +
                "END AS task_code, " +
                " exd.maintain_adress, " +
                " CASE " +
                "WHEN exd.lost_reason = 'clzr' THEN " +
                " '车辆转让或报废' " +
                "WHEN exd.lost_reason = 'rzwd' THEN " +
                " '人在外地没时间保养' " +
                "WHEN exd.lost_reason = 'glc' THEN " +
                " '过路车' " +
                "WHEN exd.lost_reason = 'chsy' THEN " +
                " '车很少用' " +
                "WHEN exd.lost_reason = 'fbr' THEN " +
                " '非车主本人接听电话' " +
                "WHEN exd.lost_reason = 'qdc' THEN " +
                " '外地车' " +
                "WHEN exd.lost_reason = 'wxzl' THEN " +
                " '维修质量' " +
                "WHEN exd.lost_reason = 'wxjg' THEN " +
                " '维修价格' " +
                "WHEN exd.lost_reason = 'wxxl' THEN " +
                " '维修效率' " +
                "WHEN exd.lost_reason = 'fwtd' THEN " +
                " '服务态度' " +
                "WHEN exd.lost_reason = 'fwhj' THEN " +
                " '服务环境' " +
                "WHEN exd.lost_reason = 'lcy' THEN " +
                " '路程远' " +
                "WHEN exd.lost_reason = 'qt' THEN " +
                " '其他' " +
                "END AS lost_reason, " +
                " case WHEN exd.intention_level ='lcwd'  THEN '里程未到'            " +
                "WHEN exd.intention_level ='jqdd'  THEN '近期到店'            " +
                "WHEN exd.intention_level ='zjtm'  THEN '最近太忙没时间进店'          " +
                "WHEN exd.intention_level ='yyyh'  THEN '已预约好近期到店' end as intention_Level,  " +
                "case WHEN exd.intention_enter='yes' then '是'  " +
                "  WHEN exd.intention_enter='no' then '否' end as intention_enter  " +
                ",case WHEN exd.ismaintained='yes' then '是'  " +
                "  WHEN exd.ismaintained='no' then '否' end as ismaintained," +
                " CASE " +
                "WHEN exd.fail_reason = 'dhtj' THEN " +
                " '电话错误停机或空号' " +
                "WHEN exd.fail_reason = 'rybf' THEN " +
                " '人员不符' " +
                "WHEN exd.fail_reason = 'wjt' THEN " +
                " '未接通' " +
                "WHEN exd.fail_reason = 'khjf' THEN " +
                " '客户拒访' " +
                "END AS fail_reason ");
        sqlBuf.append("from T_CRM_CAR_CONTACT ct                 ");
        sqlBuf.append("LEFT JOIN t_crm_car cc on ct.vin=cc.vin ");
        sqlBuf.append("LEFT JOIN t_bc_user tu on ct.sa_id=tu.id  ");
        sqlBuf.append("LEFT JOIN t_crm_car_contact_extend exd ON ct.id=exd.c_id ");
        sqlBuf.append("where  ct.company_id = ? ");
        params.add(companyId);
        //判断月 日
        if ("day".equals(type)) {
            sqlBuf.append(" and FROM_UNIXTIME(ct.create_time/1000,'%Y-%m-%d')=date_format(NOW(),'%Y-%m-%d') ");
        }
        if ("month".equals(type)) {
            if (!"".equals(mDate)) {
                sqlBuf.append(" and FROM_UNIXTIME(ct.create_time/1000,'%Y%m')=date_format(?,'%Y%m') ");
                params.add(mDate + "-01");
            } else {
                sqlBuf.append(" and FROM_UNIXTIME(ct.create_time/1000,'%Y%m')=date_format(NOW(),'%Y%m') ");
            }
        }
        //判断是预约 还是电话
        if ("tel".equals(status)) {
            sqlBuf.append(" and ct.status='1' ");
        }
        if ("reserve".equals(status)) {
            sqlBuf.append(" and ct.new_remind='1' ");
        }
        if (!"".equals(saId)) {
            sqlBuf.append(" and ct.create_user=? ");
            params.add(saId);
        }
        return this.ikpiDao.page(sqlBuf.toString(), params.toArray(), start, limit);
    }

    /**
     * 流失客服回访月报
     * 作者：徐钦勇
     */
    @Override
    public List lsVisit(String mDate, String uid, String companyId, String area, String brand) throws Exception {
        try {
            StringBuffer sqlBuf = new StringBuffer();
            List params = new ArrayList();
            if (null == mDate || mDate.length() == 0) {
                SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM");
                mDate = sf.format(new Date());
            }
            sqlBuf.append("select t.type_code lsMonth,COUNT(t.id) visitNum, COUNT(DISTINCT t.vin) cars,");
            sqlBuf.append(" COUNT(DISTINCT CASE WHEN t.`status`>0 and t.visit_result ='success' then t.vin END) visitCars, ");
            sqlBuf.append(" COUNT(if(t.status > 0 and t.visit_result='success' , true,NULL)) visitBack,");
            sqlBuf.append(" COUNT(if(t.status > 0 and t.visit_result='fail' , true,NULL)) visitNoBack,");
            sqlBuf.append(" COUNT(if(t.status = 0 , true,NULL)) noVisit,");
            sqlBuf.append(" COUNT(if(t.is_back ='1' ,true, NULL ) ) AS visitBackInTwo,t.company_id,t.o_name ");
            sqlBuf.append(" from (" +
                    "select * from (" +
                    "SELECT	ttr.*, tc.visit_result FROM ");
            sqlBuf.append("	(SELECT	ttr.*, bc.o_name FROM ");
            sqlBuf.append("	t_crm_task_real ttr,");
            sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");
            Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
            sqlBuf.append(" ) bc WHERE ttr.company_id = bc.id AND ttr.type_pcode = 'ls'");
            if (!TokenUtil.isCompanyAdmin(uid)) {
                sqlBuf.append(" AND ttr.sa_id=? ");
                params.add(uid);
            }
            sqlBuf.append(" AND ttr. STATUS > 0 AND DATE_FORMAT(ttr.expect_date, '%Y-%m') = ? ");
            params.add(mDate);
            sqlBuf.append("	) ttr, (");
            sqlBuf.append(" SELECT tc.*, tce.visit_result FROM");
            sqlBuf.append(" t_crm_car_contact tc, t_crm_car_contact_extend tce");
            sqlBuf.append("	 WHERE tc.id = tce.c_id AND tc.company_id = tce.company_id");
            sqlBuf.append("	) tc");
            sqlBuf.append(" WHERE ttr.id = tc.task_id AND ttr.company_id = tc.company_id");
            sqlBuf.append(" UNION ");
            sqlBuf.append(" SELECT ttr.*, bc.o_name,'' visit_result FROM");
            sqlBuf.append(" t_crm_task_real ttr,");
            sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");
            Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
            sqlBuf.append(" ) bc WHERE ttr.company_id = bc.id AND ttr.type_pcode = 'ls'");
            if (!TokenUtil.isCompanyAdmin(uid)) {
                sqlBuf.append(" AND ttr.sa_id=? ");
                params.add(uid);
            }
            sqlBuf.append(" AND ttr. STATUS = 0 AND DATE_FORMAT(ttr.expect_date, '%Y-%m') = ? ");
            params.add(mDate);
            sqlBuf.append(" UNION ");
            sqlBuf.append(" SELECT ttr.*, bc.o_name,'' visit_result");
            sqlBuf.append("	FROM t_crm_task_real ttr,");
            sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");
            Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
            sqlBuf.append("	) bc WHERE ttr.company_id = bc.id");
            sqlBuf.append(" AND ttr.type_pcode = 'ls'");
            sqlBuf.append(" AND ttr. STATUS >0");
            if (!TokenUtil.isCompanyAdmin(uid)) {
                sqlBuf.append(" AND ttr.sa_id=? ");
                params.add(uid);
            }
            sqlBuf.append(" AND DATE_FORMAT(ttr.expect_date, '%Y-%m') =? ");
            params.add(mDate);
            sqlBuf.append(" ) temp group by temp.id");
            sqlBuf.append(" ) t GROUP BY t.type_code,t.company_id ORDER BY t.type_code*1,t.company_id ");

            return this.ikpiDao.list(sqlBuf.toString(), params.toArray());
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList();
        }
    }

    /**
     * 流失客服回访明细
     * 作者：徐钦勇
     */
    @Override
    public Map lsVisitDetail(String uid, String start, String limit, String mDate, String companyId, String area, String brand) throws Exception {
        try {
            StringBuffer sqlBuf = new StringBuffer();
            List params = new ArrayList();
            if (null == mDate || mDate.length() == 0) {
                SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM");
                mDate = sf.format(new Date());
            }
            sqlBuf.append("SELECT car.plate_num,car.owner_name,car.owner_tel,car.owner_phone,car.run_mile statusNow," +
                    "t1.visitDate,t1.id,t1.company_id,t1.o_name,t1.vin,t1.plateNum,t1.feedback,t1.task_id,t1.lostQx," +
                    "t1.visit_result,t1.concat_fail_reason,t1.visitReasonNoBack, ");
            sqlBuf.append(" CASE " +
                    "WHEN t1.lost_reason = 'clzr' THEN " +
                    " '车辆转让或报废' " +
                    "WHEN t1.lost_reason = 'rzwd' THEN " +
                    " '人在外地没时间保养' " +
                    "WHEN t1.lost_reason = 'glc' THEN " +
                    " '过路车' " +
                    "WHEN t1.lost_reason = 'chsy' THEN " +
                    " '车很少用' " +
                    "WHEN t1.lost_reason = 'fbr' THEN " +
                    " '非车主本人接听电话' " +
                    "WHEN t1.lost_reason = 'qdc' THEN " +
                    " '外地车' " +
                    "WHEN t1.lost_reason = 'wxzl' THEN " +
                    " '维修质量' " +
                    "WHEN t1.lost_reason = 'wxjg' THEN " +
                    " '维修价格' " +
                    "WHEN t1.lost_reason = 'wxxl' THEN " +
                    " '维修效率' " +
                    "WHEN t1.lost_reason = 'fwtd' THEN " +
                    " '服务态度' " +
                    "WHEN t1.lost_reason = 'fwhj' THEN " +
                    " '服务环境' " +
                    "WHEN t1.lost_reason = 'lcy' THEN " +
                    " '路程远' " +
                    "WHEN t1.lost_reason = 'qt' THEN " +
                    " '其他' " +
                    "END AS lost_reason ");
            sqlBuf.append("FROM t_crm_car car,");
            sqlBuf.append("	(SELECT * FROM ");
            sqlBuf.append("	(SELECT ttr.*, tc.feedback,tc.task_id,tc.lostQx,tc.visit_result,tc.concat_fail_reason, ");
            sqlBuf.append("	tc.visitReasonNoBack,tc.lost_reason FROM      ");
            sqlBuf.append("	(SELECT ttr.deal_date visitDate,ttr.id,ttr.company_id,bc.o_name,ttr.vin,ttr.plateNum        ");
            sqlBuf.append("	FROM  t_crm_task_real ttr,   ");
            sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");
            Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
            sqlBuf.append("	) bc");
            sqlBuf.append("	WHERE ttr.company_id = bc.id AND ttr.type_pcode = 'ls' AND ttr. STATUS > 0 AND DATE_FORMAT(ttr.expect_date, '%Y-%m') = ? ");
            params.add(mDate);
            if (!TokenUtil.isCompanyAdmin(uid)) {
                sqlBuf.append(" AND ttr.sa_id=? ");
                params.add(uid);
            }
            sqlBuf.append(") ttr,(       ");
            sqlBuf.append("	SELECT tc.content feedback,tc.task_id,tc.company_id,tce.miss_target lostQx,tce.visit_result,");
            sqlBuf.append(" tce.concat_fail_reason, tce.concat_fail_reason visitReasonNoBack,tce.lost_reason ");
            sqlBuf.append("	FROM  t_crm_car_contact tc,t_crm_car_contact_extend tce ");
            sqlBuf.append("	WHERE ");
            sqlBuf.append(" tc.id = tce.c_id AND tc.company_id = tce.company_id) tc    ");
            sqlBuf.append("	WHERE ttr.id = tc.task_id  AND ttr.company_id = tc.company_id            ");
            sqlBuf.append("	UNION     ");
            sqlBuf.append(" SELECT  ttr.deal_date visitDate,ttr.id,ttr.company_id,bc.o_name,ttr.vin,ttr.plateNum,'' feedback," +
                    "'' task_id,'' lostQX,'' visit_result,'' concat_fail_reason,'' visitReasonNoBack,'' lost_reason ");
            sqlBuf.append(" FROM    ");
            sqlBuf.append("	t_crm_task_real ttr,     ");
            sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");
            Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
            sqlBuf.append("	) bc  ");
            sqlBuf.append("WHERE ttr.company_id = bc.id AND ttr.type_pcode = 'ls' AND ttr. STATUS = 0 AND DATE_FORMAT(ttr.expect_date, '%Y-%m') = ?");
            params.add(mDate);
            if (!TokenUtil.isCompanyAdmin(uid)) {
                sqlBuf.append(" AND ttr.sa_id=? ");
                params.add(uid);
            }
            sqlBuf.append(" UNION   ");
            sqlBuf.append(" SELECT  ttr.deal_date visitDate,ttr.id,ttr.company_id,bc.o_name,ttr.vin,ttr.plateNum,'' feedback," +
                    "'' task_id,'' lostQX,'' visit_result,'' concat_fail_reason,'' visitReasonNoBack,''lost_reason  ");
            sqlBuf.append("	FROM   t_crm_task_real ttr,");
            sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");
            Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
            sqlBuf.append(") bc");
            sqlBuf.append("	WHERE  ttr.company_id = bc.id  AND ttr.type_pcode = 'ls' AND ttr. STATUS > 0  AND DATE_FORMAT(ttr.expect_date, '%Y-%m') = ?");
            params.add(mDate);
            if (!TokenUtil.isCompanyAdmin(uid)) {
                sqlBuf.append(" AND ttr.sa_id=? ");
                params.add(uid);
            }
            sqlBuf.append("	 ) t0  GROUP BY  t0.id  ) t1 ");
            sqlBuf.append("WHERE car.vin = t1.vin AND car.company_id = t1.company_id  order by t1.visit_result DESC");
            return this.ikpiDao.page(sqlBuf.toString(), params.toArray(), start, limit);
        } catch (Exception e) {
            e.printStackTrace();
            return new HashMap();
        }
    }

    /**
     * 流失回厂数
     * 作者：徐钦勇
     */
    @Override
    public Map carLostBack(String uid, String start, String limit, String mDate, String companyId, String area, String brand) throws Exception {
        try {
            StringBuffer sqlBuf = new StringBuffer();
            List params = new ArrayList();
            String cid = TokenUtil.getCompanyId(uid);
            if (null == mDate || mDate.length() == 0) {
                SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM");
                mDate = sf.format(new Date());
            }
            sqlBuf.append("select * from ( SELECT    ");
            sqlBuf.append("     IF(ttr.sa_id IS NULL OR ttr.sa_id = '',  ");
            sqlBuf.append("         '未分配',  ");
            sqlBuf.append("         IF(ttr.u_name IS NULL,  ");
            sqlBuf.append("             '已离职',  ");
            sqlBuf.append("             ttr.u_name)) AS SA,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '6', TRUE, NULL)) AS sixTaskNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '6'  ");
            sqlBuf.append("             AND ttr.is_back = '1',  ");
            sqlBuf.append("         TRUE,  ");
            sqlBuf.append("         NULL)) AS sixTaskBackNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '7', TRUE, NULL)) AS sevenTaskNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '7'  ");
            sqlBuf.append("             AND ttr.is_back = '1',  ");
            sqlBuf.append("         TRUE,  ");
            sqlBuf.append("         NULL)) AS sevenTaskBackNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '8', TRUE, NULL)) AS eightTaskNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '8'  ");
            sqlBuf.append("             AND ttr.is_back = '1',  ");
            sqlBuf.append("         TRUE,  ");
            sqlBuf.append("         NULL)) AS eightTaskBackNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '9', TRUE, NULL)) AS nineTaskNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '9'  ");
            sqlBuf.append("             AND ttr.is_back = '1',  ");
            sqlBuf.append("         TRUE,  ");
            sqlBuf.append("         NULL)) AS nineTaskBackNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '10', TRUE, NULL)) AS tenTaskNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '10'  ");
            sqlBuf.append("             AND ttr.is_back = '1',  ");
            sqlBuf.append("         TRUE,  ");
            sqlBuf.append("         NULL)) AS tenTaskBackNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '11', TRUE, NULL)) AS elevTaskNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '11'  ");
            sqlBuf.append("             AND ttr.is_back = '1',  ");
            sqlBuf.append("         TRUE,  ");
            sqlBuf.append("         NULL)) AS elevTaskBackNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '12', TRUE, NULL)) AS twlfTaskNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '12' and ttr.is_back='1', TRUE, NULL)) AS twlfTaskBackNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '13', TRUE, NULL)) AS thirTaskNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_code = '13' and ttr.is_back='1', TRUE, NULL)) AS thirTaskBackNum,  ");

            sqlBuf.append("     COUNT(IF(ttr.type_pcode = 'ls', TRUE, NULL)) AS lsTaskNum,  ");
            sqlBuf.append("     COUNT(IF(ttr.type_pcode = 'ls'  ");
            sqlBuf.append("             AND ttr.is_back = '1',  ");
            sqlBuf.append("         TRUE,  ");
            sqlBuf.append("         NULL)) AS lsTaskBackNum,ttr.sa_id,bc.o_name,ttr.company_id  ");
            sqlBuf.append(" FROM  ");
            sqlBuf.append(" (select ttr.*,tu.u_name from " +


                    " ( SELECT * FROM t_crm_task_real WHERE 1 = 1 AND DATE_FORMAT(expect_date, '%Y-%m') = ? GROUP BY vin ) ttr ");
                    params.add(mDate);

            sqlBuf.append(" LEFT JOIN t_bc_user tu ON ttr.sa_id = tu.id ) ttr ");
            sqlBuf.append(" , ");
            sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");

            Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
            sqlBuf.append(" ) bc ");
            sqlBuf.append("where ttr.company_id=bc.id and   ");
            sqlBuf.append("     left(ttr.expect_date,7) = ?  ");
            params.add(mDate);
            if (!TokenUtil.isCompanyAdmin(uid)) {
                sqlBuf.append(" and ttr.sa_id = ? ");
                params.add(uid);
            }
            sqlBuf.append(" GROUP BY IF(ttr.sa_id IS NULL OR ttr.sa_id = '',  ");
            sqlBuf.append("     '未分配',  ");
            sqlBuf.append("     IF(ttr.u_name IS NULL,  ");
            sqlBuf.append("         '已离职',  ");
            sqlBuf.append("         ttr.u_name)),ttr.company_id  ");


            sqlBuf.append(" ORDER BY ttr.sa_id  ) temp  where temp.lsTaskNum > 0 ");


            return this.ikpiDao.page(sqlBuf.toString(), params.toArray(), start, limit);
        } catch (Exception e) {
            e.printStackTrace();
            return new HashMap();
        }
    }

    /**
     * 保养回厂率
     * 作者：徐钦勇
     */
    @Override
    public List<Map<String, Object>> byBackRate(String uid, String start, String limit, String mDate, String companyId,
                                                String area, String brand) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        //取 companyId下的所有部门ID
        deptList = new ArrayList<>();
        deptList.add(companyId);
        if (null == mDate || mDate.length() == 0) {
            SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM");
            mDate = sf.format(new Date());
        }
        sqlBuf.append("SELECT * FROM ( SELECT if(ttr.sa_id is null,'未分配',if(ttr.u_name is null,'已离职',ttr.u_name)) as u_name, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='sy_mbr',TRUE,NULL)) sy,  ");
        sqlBuf.append("COUNT(IF(ttr.type_code='sy_mbr' and ttr.is_back='1',TRUE,NULL)) syBack, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='sb_mbr',TRUE,NULL)) sb,  ");
        sqlBuf.append("COUNT(IF(ttr.type_code='sb_mbr' and ttr.is_back='1',TRUE,NULL)) sbBack, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='sb_mby',TRUE,NULL)) sby, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='sb_mby' and ttr.is_back='1',TRUE,NULL)) sbyBack, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='eb_mbr',TRUE,NULL)) eb, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='eb_mbr' and ttr.is_back='1',TRUE,NULL)) ebBack, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='eb_mby',TRUE,NULL)) eby, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='eb_mby' and ttr.is_back='1',TRUE,NULL)) ebyBack, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='db_mbr',TRUE,NULL)) db, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='db_mbr' and ttr.is_back='1',TRUE,NULL)) dbBack, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='db_mby',TRUE,NULL)) dby, ");
        sqlBuf.append("COUNT(IF(ttr.type_code='db_mby' and ttr.is_back='1',TRUE,NULL)) dbyBack, ");
        //任务总计
        sqlBuf.append("COUNT(ttr.id) taskNum, ");
        //回厂的总数量
        sqlBuf.append("COUNT( IF(ttr.is_back='1',TRUE,NULL)) byNum,ttr.sa_id,ttr.company_id,bc.o_name ");
        sqlBuf.append("FROM (select ttr.*,tu.u_name from t_crm_task_real ttr LEFT JOIN t_bc_user tu ON ttr.sa_id=tu.id ");

        sqlBuf.append("WHERE DATE_FORMAT(ttr.expect_date, '%Y-%m') = ? AND ttr.type_pcode = 'by' GROUP BY ttr.vin ) ttr ");
        params.add(mDate);

        sqlBuf.append(",");
        sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
        sqlBuf.append(" ) bc ");

        sqlBuf.append(" WHERE ttr.company_id=bc.id and DATE_FORMAT(ttr.expect_date, '%Y-%m') = ? and ttr.type_pcode ='by' ");
        params.add(mDate);
        /**
         * 非管理员查看自己
         * **/
        if (!TokenUtil.isCompanyAdmin(uid)) {
            sqlBuf.append(" and ttr.sa_id = ? ");
            params.add(uid);
        }

        sqlBuf.append("GROUP BY ttr.sa_id,ttr.company_id ) a where taskNum > 0");
        return this.ikpiDao.list(sqlBuf.toString(), params.toArray());

    }

    /**
     * 保养回厂率数据穿透明细
     *
     * @param uid
     * @param start
     * @param limit
     * @param mDate
     * @param typeCode
     * @param isBack
     * @param saId
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, Object> taskBackRateDetail(String uid, String start, String limit, String mDate,
                                                  String typePcode, String typeCode, String isBack, String saId, String status, String companyId) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        this.taskBackReteDetailToSql(uid, sqlBuf, params, saId, mDate, typePcode, typeCode, isBack, status, companyId);
        return this.ikpiDao.page(sqlBuf.toString(), params.toArray(), start, limit);

    }

    /**
     * 重载 不带完成状态的查询
     *
     * @param uid
     * @param start
     * @param limit
     * @param mDate
     * @param typePcode
     * @param typeCode
     * @param isBack
     * @param saId
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, Object> taskBackRateDetail(String uid, String start, String limit, String mDate,
                                                  String typePcode, String typeCode, String isBack, String saId, String companyId) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        this.taskBackReteDetailToSql(uid, sqlBuf, params, saId, mDate, typePcode, typeCode, isBack, "", companyId);
        return this.ikpiDao.page(sqlBuf.toString(), params.toArray(), start, limit);

    }

    private void taskBackReteDetailToSql(String uid, StringBuffer sqlBuf, List<Object> params, String saId, String mDate,
                                         String typePcode, String typeCode, String isBack, String status, String companyId) throws Exception {
        sqlBuf.append(" SELECT * FROM ( SELECT task.content,task.deal_date,task.expect_date,task.is_back,task.status,task.vin,task.plateNum,car.owner_name,car.owner_tel, " +
                " car.owner_phone,car.brand,car.series,car.model,car.configure, " +
                "case when task.type_code='zdy' then '自定义'" +
                "    else paramDef.name                " +
                "    end     " +
                " typeName,tuser.u_name uname " +
                " FROM ( SELECT * FROM t_crm_task_real where 1=1 ");

           /* sqlBuf.append(" and type_pcode=?  ");
            params.add(typePcode);*/

        sqlBuf.append(" AND DATE_FORMAT(expect_date, '%Y%m') = DATE_FORMAT(?, '%Y%m') GROUP BY vin ");
        Long l = DateUtils.monthSToLongTime(mDate);
        String dateStr = DateUtils.formatLongTimeByYYYYMMDD(l);
        boolean isAdmin = TokenUtil.isCompanyAdmin(uid);
        params.add(dateStr);
         sqlBuf.append(" )task LEFT JOIN t_crm_car car " +
                " ON task.vin = car.vin " +
                " AND task.company_id = car.company_id " +
                " LEFT JOIN t_bc_param_def paramDef ON task.type_code=paramDef.code " +
                " LEFT JOIN t_bc_user tuser ON task.sa_id=tuser.id " +
                " WHERE " +
                " DATE_FORMAT(task.expect_date,'%Y%m')=DATE_FORMAT(?,'%Y%m') " +
                " and task.company_id=? and task.type_pcode=?");

        params.add(dateStr);
        params.add(companyId);
        params.add(typePcode);
        if (!"".equals(saId)) {
            if ("未分配".equals(saId)) {
                sqlBuf.append(" and (task.sa_id is NULL or task.sa_id='') ");
            } else {
                sqlBuf.append(" and task.sa_id=? ");
                params.add(saId);
            }
        }
        if (!isAdmin) {
            sqlBuf.append(" and task.sa_id=? ");
            params.add(uid);
        }
        if (!"".equals(typeCode)) {
            sqlBuf.append(" and task.type_code=?  ");
            params.add(typeCode);
        }
        //是否返厂
        if (!"".equals(isBack)) {
            sqlBuf.append(" and task.is_back=? ");
            params.add(isBack);
        }
        //增加完成状态
        if (!"".equals(status)) {
            //0默认未完成 2 已关闭
            if ("0".equals(status) || "2".equals(status)) {
                sqlBuf.append(" and task.status=? ");
                params.add(status);
            }
            //1查询已完成的 也就是所有不是0的
            if ("1".equals(status)) {
                sqlBuf.append(" and task.status>0 ");
            }
        }
        sqlBuf.append(" GROUP BY task.vin ) t");

    }


    /**
     * 车辆回厂率
     * 作者：徐钦勇
     */
    @Override
    public Map CarBackRate(String uid, String start, String limit, String mDate) throws Exception {
        try {
            StringBuffer sqlBuf = new StringBuffer();
            List params = new ArrayList();
            String cid = TokenUtil.getCompanyId(uid);
            if (null == mDate || mDate.length() == 0) {
                SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM");
                mDate = sf.format(new Date());
            }
            sqlBuf.append("SELECT * FROM (SELECT tu.u_name, ");
            sqlBuf.append("COUNT(IF(ttr.type_code='sb_mbr' and ttr.is_back='1',TRUE,NULL)) sb, ");
            sqlBuf.append("COUNT(IF(ttr.type_code='sb_mby' and ttr.is_back='1',TRUE,NULL)) sby, ");
            sqlBuf.append("COUNT(IF(ttr.type_code='eb_mbr' and ttr.is_back='1',TRUE,NULL)) eb, ");
            sqlBuf.append("COUNT(IF(ttr.type_code='eb_mby' and ttr.is_back='1',TRUE,NULL)) eby, ");
            sqlBuf.append("COUNT(IF(ttr.type_code='db_mbr' and ttr.is_back='1',TRUE,NULL)) db, ");
            sqlBuf.append("COUNT(IF(ttr.type_code='db_mby' and ttr.is_back='1',TRUE,NULL)) dby, ");
            sqlBuf.append("COUNT(IF(ttr.type_code in ('sb_mbr','sb_mby','eb_mbr','eb_mby','db_mbr','db_mbr') and ttr.is_back='1',TRUE,NULL)) bySum, ");
            sqlBuf.append("COUNT(IF(ttr.type_pcode ='ls' and ttr.is_back='1',TRUE,NULL)) ls, ");
            sqlBuf.append("COUNT(IF( ttr.is_back='1' and (ttr.type_code in ('sb_mbr','sb_mby','eb_mbr','eb_mby','db_mbr','db_mbr') or ttr.type_pcode ='ls' ),TRUE,NULL)) backSum ");
            sqlBuf.append("FROM t_crm_task_real ttr  LEFT JOIN  t_bc_user tu   ON ttr.sa_id = tu.id and DATE_FORMAT(ttr.expect_date, '%Y-%m') = ? ");
            sqlBuf.append("WHERE ttr.company_id=?  ");
            sqlBuf.append("GROUP BY tu.u_name )  AS A where A.bySum > 0 ");
            params.add(mDate);
            params.add(cid);
            return this.ikpiDao.page(sqlBuf.toString(), params.toArray(), start, limit);
        } catch (Exception e) {
            e.printStackTrace();
            return new HashMap();
        }
    }

    /**
     * 工单匹配率
     * 作者：徐钦勇
     */
    @Override
    public List<Map<String, Object>> WorkOrderMatchRate(String uid, String start, String limit, String mDate, String mDate2,String companyId,
                                                        String area, String brand) throws
            Exception {

        //StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        String cid = TokenUtil.getCompanyId(uid);
        if (StringUtils.isEmpty(companyId)) {
            companyId = cid;
        }
       /* if (null == mDate || mDate.length() == 0) {
            SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM-dd");
            mDate = sf.format(new Date());
        }*/
        String sql = "SELECT temp2.company_id,bo.o_name," +
                " temp2.receptor, " +
                " count(temp2.vin) AS jcs, " +
                " CONCAT( " +
                "  FORMAT( " +
                "   COUNT( " +
                " " +
                "    IF ( " +
                "     temp2.receptor = temp2.u_name, " +
                "     TRUE, " +
                "     NULL " +
                "    ) " +
                "   ) * 100 / COUNT(temp2.vin), " +
                "   2 " +
                "  ), " +
                "  '%' " +
                " ) AS ppl, " +
                " COUNT( " +
                " " +
                "  IF ( " +
                "   temp2.receptor = temp2.u_name, " +
                "   TRUE, " +
                "   NULL " +
                "  ) " +
                " ) AS pps, " +
                " COUNT( " +
                " " +
                "  IF (temp2.u_name IS NULL, TRUE, NULL) " +
                " ) AS wfp " +
                "FROM " +
                " ( " +
                "  SELECT " +
                "   temp.*, " +
                "   u.id, " +
                "   u.u_name " +
                "  FROM " +
                "   ( " +
                "    SELECT DISTINCT " +
                "     o.company_id, " +
                "     o.vin, " +
                "     o.receptor, " +
                "     FROM_UNIXTIME(balance_date / 1000, '%Y-%m-%d') AS jcrq " +
                "    FROM " +
                "     t_crm_car_workorder o " +
                "    WHERE " +
                "     o.company_id = ? ";
                params.add(companyId);
                if(!mDate.isEmpty()){
                    sql += "    AND FROM_UNIXTIME(balance_date / 1000, '%Y-%m-%d') >= ? ";
                    params.add(mDate);
                }
                if(!mDate2.isEmpty()){
                    sql += "       AND FROM_UNIXTIME(balance_date / 1000, '%Y-%m-%d') <= ? ";
                    params.add(mDate2);
                }

                if(mDate.isEmpty() && mDate2.isEmpty()){
                    sql += "    AND FROM_UNIXTIME(balance_date / 1000, '%Y-%m-%d') = ? ";
                    SimpleDateFormat spt = new SimpleDateFormat("YYYY-MM-dd");
                    params.add(spt.format(new Date()));
                }



                sql += "   ) temp " +
                "  LEFT JOIN t_crm_car_conf_detail d ON temp.vin = d.vin " +
                "  AND temp.company_id = d.company_id " +
                "  AND d.role_id = '" + SA_ROLE_ID + "' ";

        sql += "  LEFT JOIN t_bc_user u ON d.u_id = u.id" +
                "  " +
                "  WHERE " +
                "   temp.company_id = ? " +
                "  ";


        params.add(companyId);
        //非管理员查看自己
//        if (cid.equals(companyId)&& !TokenUtil.isCompanyAdmin(uid)) {
//            sql+=" and  d.u_id =  ? ";
//            params.add(uid);
//        }
        sql += " ) temp2 LEFT JOIN t_bc_organ bo ON bo.id = temp2.company_id" +
                " GROUP BY temp2.receptor ";
               // " temp2.receptor, " +
                //" temp2.jcrq ";


        // sqlBuf.append(" GROUP BY O.RECEPTOR,O.company_id ");

        return this.ikpiDao.list(sql, params.toArray());

    }

    /**
     * 月度目标任务
     * 作者：徐钦勇
     */
    @Override
    public Map MonthBackTask(String uid, String start, String limit, String mDate, String companyId, String area, String brand) throws Exception {
        try {
            StringBuffer sqlBuf = new StringBuffer();
            List params = new ArrayList();
            String cid = TokenUtil.getCompanyId(uid);
            if (null == mDate || mDate.length() == 0) {
                SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM");
                mDate = sf.format(new Date());
            }
            sqlBuf.append("SELECT * FROM ( SELECT temp2.*,               ");
            sqlBuf.append("CASE temp2.taskTotal WHEN 0 THEN '0.00%' ELSE CONCAT(                       ");
            sqlBuf.append("ROUND(temp2.taskTotalDone/temp2.taskTotal*100,2),'%') END taskTotalDoneRate ");
            sqlBuf.append("FROM(SELECT temp.*,       ");
            sqlBuf.append("CASE temp.sbTask WHEN 0 then '0.00%' ELSE     ");
            sqlBuf.append("CONCAT(ROUND(temp.sbTaskDone/temp.sbTask*100,2),'%') END sbTaskDoneRate,    ");
            sqlBuf.append("CASE temp.sbyTask WHEN 0 then '0.00%' ELSE    ");
            sqlBuf.append("CONCAT(ROUND(temp.sbyTaskDone/temp.sbyTask*100,2),'%') END sbyTaskDoneRate, ");
            sqlBuf.append("CASE temp.ebTask WHEN 0 then '0.00%' ELSE     ");
            sqlBuf.append("CONCAT(ROUND(temp.ebTaskDone/temp.ebTask*100,2),'%') END ebTaskDoneRate,    ");
            sqlBuf.append("CASE temp.ebyTask WHEN 0 then '0.00%' ELSE    ");
            sqlBuf.append("CONCAT(ROUND(temp.ebyTaskDone/temp.ebyTask*100,2),'%') END ebyTaskDoneRate, ");
            sqlBuf.append("CASE temp.dbTask WHEN 0 then '0.00%' ELSE     ");
            sqlBuf.append("CONCAT(ROUND(temp.dbTaskDone/temp.dbTask*100,2),'%') END dbTaskDoneRate,    ");
            sqlBuf.append("CASE temp.dbyTask WHEN 0 then '0.00%' ELSE    ");
            sqlBuf.append("CONCAT(ROUND(temp.dbyTaskDone/temp.dbyTask*100,2),'%') END dbyTaskDoneRate ");
//            sqlBuf.append("temp.taskTotal taskTotal,                           ");
//            sqlBuf.append("temp.taskTotalDone taskTotalDone            ");
            sqlBuf.append("FROM ( SELECT if(ttr.sa_id is null,'未分配',if(ttr.u_name is null,'已离职',ttr.u_name)) as u_name, ");
            sqlBuf.append("COUNT(IF(ttr.type_code='sb_mbr',TRUE,NULL)) sbTask,                         ");
            sqlBuf.append("COUNT(IF(ttr.type_code='sb_mbr' and ttr.status > 0,TRUE,NULL)) sbTaskDone,  ");
            sqlBuf.append("COUNT(IF(ttr.type_pcode='by',TRUE,NULL)) taskTotal,  ");
            sqlBuf.append("COUNT(IF(ttr.type_pcode='by' and ttr.status > 0,TRUE,NULL)) taskTotalDone,  ");
            sqlBuf.append("COUNT(IF(ttr.type_code='sb_mby',TRUE,NULL)) sbyTask,                        ");
            sqlBuf.append("COUNT(IF(ttr.type_code='sb_mby' and ttr.status > 0,TRUE,NULL)) sbyTaskDone, ");
            sqlBuf.append("COUNT(IF(ttr.type_code='eb_mbr',TRUE,NULL)) ebTask,                         ");
            sqlBuf.append("COUNT(IF(ttr.type_code='eb_mbr' and ttr.status > 0,TRUE,NULL)) ebTaskDone,  ");
            sqlBuf.append("COUNT(IF(ttr.type_code='eb_mby',TRUE,NULL)) ebyTask,                        ");
            sqlBuf.append("COUNT(IF(ttr.type_code='eb_mby' and ttr.status > 0,TRUE,NULL)) ebyTaskDone, ");
            sqlBuf.append("COUNT(IF(ttr.type_code='db_mbr',TRUE,NULL)) dbTask,                         ");
            sqlBuf.append("COUNT(IF(ttr.type_code='db_mbr' and ttr.status > 0,TRUE,NULL)) dbTaskDone,  ");
            sqlBuf.append("COUNT(IF(ttr.type_code='db_mby',TRUE,NULL)) dbyTask,                        ");
            sqlBuf.append("COUNT(IF(ttr.type_code='db_mby' and ttr.status > 0,TRUE,NULL)) dbyTaskDone,ttr.sa_id,bc.o_name,ttr.company_id  ");
            sqlBuf.append("FROM (select ttr.*,tu.u_name from ( SELECT * FROM t_crm_task_real WHERE 1=1 " +
                    " AND DATE_FORMAT(expect_date, '%Y-%m') = ? ");
                    params.add(mDate);
                    sqlBuf.append(" GROUP BY vin ) ttr  LEFT JOIN    t_bc_user tu ON ttr.sa_id = tu.id )ttr ");
            sqlBuf.append(",");
            sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");
            Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
            sqlBuf.append(" ) bc ");

            sqlBuf.append("WHERE ttr.company_id=bc.id and DATE_FORMAT(ttr.expect_date, '%Y-%m')= ?  ");
            params.add(mDate);
            if (!TokenUtil.isCompanyAdmin(uid)) {
                sqlBuf.append(" AND ttr.sa_id = ? ");
                params.add(uid);
            }
            sqlBuf.append(" GROUP BY ttr.sa_id,ttr.company_id ) as temp ) as temp2 where temp2.taskTotal > 0 ) as  a                             ");

            return this.ikpiDao.page(sqlBuf.toString(), params.toArray(), start, limit);
        } catch (Exception e) {
            e.printStackTrace();
            return new HashMap();
        }
    }

    //伪递归查询公司下所有部门
    private List<Map<String, Object>> buildDeptChild(String opId) throws Exception {
        String sqlStr = "select * from t_bc_organ  where o_p_id = ? ";
        List<Map<String, Object>> child = this.ikpiDao.list(sqlStr, opId);
        return buildDeptParent(child);
    }

    private List<Map<String, Object>> buildDeptParent(List<Map<String, Object>> paranet) throws Exception {
        if (paranet.size() == 0) {
            return paranet;
        }
        String pId;
        for (Map<String, Object> temp : paranet) {
            pId = String.valueOf(temp.get("id"));
            List<Map<String, Object>> childList = buildDeptChild(pId);
            temp.put("child", childList);
            deptList.add(pId);
        }
        return paranet;
    }

    /**
     * 事故车到店率
     * 作者：侯念晨
     */
    @Override
    public List<Map<String, Object>> CarBack(String uid, String yDate, String companyId, String area, String brand) throws Exception {
        List<Object> params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select sg.*,bc.o_name from T_CRM_KPI_SG sg,");
        sql.append(" (select * from t_bc_organ where 1=1 ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" )bc ");
        sql.append("  WHERE bc.id=sg.company_id ");
        if ("".equals(yDate)) {
            yDate = DateUtils.getTimeStr("yyyy");
        }
        sql.append(" and sg.count_year = ?");
        params.add(yDate);
        sql.append(" group by sg.company_id,sg.count_tag ");
        //--------------------------------------------------
        sql.append("UNION" +
                " select sg.id, sg.create_time, sg.create_user, sg.update_time, sg.update_user, sg.company_id, ' 合计事故车数 ' as count_tag,  sg.count_year, " +
                " SUM(sg.eight), SUM(sg.eleven), SUM(sg.five), SUM(sg.four), SUM(sg.nine), SUM(sg.one), SUM(sg.seven), SUM(sg.six), SUM(sg.ten), SUM(sg.three)," +
                " SUM(sg.twelve), SUM(sg.two), sg.dept_id, sg.base_year,bc.o_name from T_CRM_KPI_SG sg,");
        sql.append(" (select * from t_bc_organ where 1=1 ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" )bc ");
        sql.append("  WHERE bc.id=sg.company_id ");
        sql.append("  and sg.count_tag='事故车数' ");
        if (!"".equals(yDate)) {
            sql.append(" and sg.count_year = ?");
            params.add(yDate);
        }
        sql.append(" group by sg.company_id ");
        //--------------------------------------------------
        sql.append("UNION" +
                " select sg.id, sg.create_time, sg.create_user, sg.update_time, sg.update_user, sg.company_id, '合计到店数' as count_tag,  sg.count_year, " +
                " SUM(sg.eight), SUM(sg.eleven), SUM(sg.five), SUM(sg.four), SUM(sg.nine), SUM(sg.one), SUM(sg.seven), SUM(sg.six), SUM(sg.ten), SUM(sg.three)," +
                " SUM(sg.twelve), SUM(sg.two), sg.dept_id, sg.base_year,bc.o_name from T_CRM_KPI_SG sg,");
        sql.append(" (select * from t_bc_organ where 1=1 ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" )bc ");
        sql.append("  WHERE bc.id=sg.company_id ");
        sql.append("  and sg.count_tag='到店数' ");
        if (!"".equals(yDate)) {
            sql.append(" and sg.count_year = ?");
            params.add(yDate);
        }
        sql.append(" group by sg.company_id ");
        //--------------------------------------------------
        sql.append("UNION" +
                " select sg.id, sg.create_time, sg.create_user, sg.update_time, sg.update_user, sg.company_id, '合计到店率' as count_tag,  sg.count_year, " +
                " SUM(sg.eight), SUM(sg.eleven), SUM(sg.five), SUM(sg.four), SUM(sg.nine), SUM(sg.one), SUM(sg.seven), SUM(sg.six), SUM(sg.ten), SUM(sg.three)," +
                " SUM(sg.twelve), SUM(sg.two), sg.dept_id, sg.base_year,bc.o_name from T_CRM_KPI_SG sg,");
        sql.append(" (select * from t_bc_organ where 1=1 ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" )bc ");
        sql.append(" WHERE bc.id=sg.company_id ");
        sql.append(" and sg.count_tag='到店率' ");
        if (!"".equals(yDate)) {
            sql.append(" and sg.count_year = ?");
            params.add(yDate);
        }
        sql.append(" group by sg.company_id ");
        /**
         * 加入年份
         */

        return this.ikpiDao.list(sql.toString(), params.toArray());

    }

    /**
     * 出保车辆到店率
     * 作者：侯念晨
     */
    @Override
    public List<Map<String, Object>> cbcarback(String uid, String yDate, String sa, String companyId, String area, String brand) throws Exception {
        List<Object> params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        boolean isAdmin = TokenUtil.isCompanyAdmin(uid);
        if (!isAdmin) {
            Map userMap = TokenUtil.getUser(uid);
            sa = String.valueOf(userMap.get("uName"));
        }
        sql.append("select * from (select zb.count_year, zb.sa,zb.count_tag,zb.one,zb.two,zb.three,zb.four,zb.five,zb.six," +
                "zb.seven,zb.eight,zb.nine,zb.ten,zb.eleven,zb.twelve,zb.base_year," +
                "zb.sa_id,zb.company_id,bc.o_name from T_CRM_KPI_ZB zb,");
        this.cbcarBackOtherQueryParams(sql, params, uid, companyId, area, brand, yDate, sa, "");
        sql.append(" union ");
        sql.append(" select '' count_year, '0合计' sa, '车辆数' count_tag, sum(zb.one) as one," +
                " sum(zb.two) as two , sum(zb.three) as three , sum(zb.four) as four , sum(zb.five) as five ," +
                " sum(zb.six) as six,sum(zb.seven) as seven , sum(zb.eight) as eight , sum(zb.nine) as nine , sum(zb.ten) as ten ," +
                " sum(zb.eleven) as eleven, sum(zb.twelve) as twelve,zb.base_year,zb.sa_id,zb.company_id,bc.o_name" +
                " from T_CRM_KPI_ZB zb,");
        this.cbcarBackOtherQueryParams(sql, params, uid, companyId, area, brand, yDate, sa, "车辆数");
        sql.append(" union ");
        sql.append("select '' count_year, '0合计' sa, '回店数'  count_tag, sum(zb.one) as one," +
                " sum(zb.two) as two , sum(zb.three) as three , sum(zb.four) as four , sum(zb.five) as five ," +
                " sum(zb.six) as six,sum(zb.seven) as seven , sum(zb.eight) as eight , sum(zb.nine) as nine , sum(zb.ten) as ten ," +
                " sum(zb.eleven) as eleven,sum(zb.twelve) as twelve,zb.base_year,zb.sa_id,zb.company_id,bc.o_name" +
                " from T_CRM_KPI_ZB zb,");
        this.cbcarBackOtherQueryParams(sql, params, uid, companyId, area, brand, yDate, sa, "回店数");
        sql.append(" union ");
        sql.append("select '' count_year, '0合计' sa, '到店率'  count_tag, sum(zb.one) as one," +
                " sum(zb.two) as two , sum(zb.three) as three , sum(zb.four) as four , sum(zb.five) as five ," +
                " sum(zb.six) as six,sum(zb.seven) as seven , sum(zb.eight) as eight , sum(zb.nine) as nine , sum(zb.ten) as ten ," +
                " sum(zb.eleven) as eleven," +
                " sum(zb.twelve) as twelve,zb.base_year,zb.sa_id,zb.company_id,bc.o_name" +
                " from T_CRM_KPI_ZB zb,");
        this.cbcarBackOtherQueryParams(sql, params, uid, companyId, area, brand, yDate, sa, "到店率");
        sql.append(" ) ");
        sql.append("AS t WHERE 1=1 ORDER BY sa desc, count_tag DESC ");
        return this.ikpiDao.list(sql.toString(), params.toArray());

    }

    /**
     * 出保客户到店率公共查询条件
     *
     * @param sql
     * @param params
     * @param uid
     * @param companyId
     * @param area
     * @param brand
     * @param yDate
     * @param sa
     * @param tag
     */
    private void cbcarBackOtherQueryParams(StringBuffer sql, List<Object> params, String uid, String companyId, String area,
                                           String brand, String yDate, String sa, String tag) {
        sql.append(" (select * from t_bc_organ where 1=1 ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" )bc ");
        sql.append(" WHERE bc.id=zb.company_id ");
        if (!"".equals(tag)) {
            sql.append(" and zb.count_tag = '" + tag + "' ");
        }
        if (!"".equals(yDate)) {
            sql.append(" and zb.count_year = ? ");
            params.add(yDate);
        }
        if (!"".equals(sa)) {
            sql.append(" and zb.sa=? ");
            params.add(sa);
        }
        sql.append(" group by  zb.company_id");
        if ("".equals(tag)) {
            sql.append(",zb.sa,zb.count_tag ");
        }

    }

    /**
     * 客户定保到店率
     * 作者：侯念晨
     */
    @Override
    public List<Map<String, Object>> dbcarback(String uid, String yDate, String sa, String companyId, String area, String brand) throws Exception {
        List<Object> params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select * from (");
        sql.append("select db.count_year, db.sa,db.count_tag,db.one,db.two,db.three,db.four,db.five,db.six,db.seven,db.eight," +
                "db.nine,db.ten,db.eleven,db.twelve,db.base_year," +
                "db.sa_id,bc.o_name,db.company_id from T_CRM_KPI_DB db,");
        sql.append(" (select * from t_bc_organ where 1=1 ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" )bc ");
        sql.append(" where db.company_id=bc.id ");

        boolean isAdmin = TokenUtil.isCompanyAdmin(uid);
        if (!isAdmin) {
            Map userMap = TokenUtil.getUser(uid);
            sa = String.valueOf(userMap.get("uName"));
        }
        if (!"".equals(yDate)) {
            sql.append(" and db.count_year = ? ");
            params.add(yDate);
        }
        if (!"".equals(sa)) {
            sql.append(" and db.sa = ? ");
            params.add(sa);
        }
        sql.append(" group by db.company_id,db.sa,db.count_tag ");
        //------------------------------------------------
        sql.append(" union ");
        sql.append(" select db.count_year count_year, '0合计' sa, '车辆数' count_tag, sum(db.one) as one," +
                " sum(db.two) as two , sum(db.three) as three , sum(db.four) as four , sum(db.five) as five ," +
                " sum(db.six) as six,sum(db.seven) as seven , sum(db.eight) as eight , sum(db.nine) as nine , sum(db.ten) as ten ," +
                " sum(db.eleven) as eleven,sum(db.twelve) as twelve,db.base_year,db.sa_id,bc.o_name,db.company_id from T_CRM_KPI_DB db,");
        sql.append(" (select * from t_bc_organ where 1=1 ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" )bc ");
        sql.append(" where db.company_id=bc.id ");
        sql.append(" and db.count_tag = '车辆数' ");
        if (!"".equals(yDate)) {
            sql.append(" and db.count_year = ? ");
            params.add(yDate);
        }
        if (!"".equals(sa)) {
            sql.append(" and db.sa = ? ");
            params.add(sa);
        }
        sql.append(" group by db.company_id ");
        //-----------------------------------------------
        sql.append(" union ");
        sql.append(" select db.count_year count_year, '0合计' sa, '回店数'  count_tag, sum(db.one) as one," +
                " sum(db.two) as two , sum(db.three) as three , sum(db.four) as four , sum(db.five) as five ," +
                " sum(db.six) as six,sum(db.seven) as seven , sum(db.eight) as eight , sum(db.nine) as nine ," +
                " sum(db.ten) as ten ,sum(db.eleven) as eleven," +
                " sum(db.twelve) as twelve,db.base_year,db.sa_id,bc.o_name,db.company_id from T_CRM_KPI_DB db,");
        sql.append(" (select * from t_bc_organ where 1=1 ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" )bc ");
        sql.append(" where db.company_id=bc.id ");
        sql.append(" and db.count_tag = '回店数' ");
        if (!"".equals(yDate)) {
            sql.append(" and db.count_year = ? ");
            params.add(yDate);
        }
        if (!"".equals(sa)) {
            sql.append(" and db.sa = ? ");
            params.add(sa);
        }
        sql.append("  group by db.company_id ");
        //------------------------------------------------
        sql.append(" union ");
        sql.append(" select db.count_year count_year, '0合计' sa, '到店率'  count_tag, sum(db.one) as one," +
                " sum(db.two) as two , sum(db.three) as three , sum(db.four) as four , sum(db.five) as five ," +
                " sum(db.six) as six,sum(db.seven) as seven , sum(db.eight) as eight , sum(db.nine) as nine , sum(db.ten) as ten ," +
                " sum(db.eleven) as eleven," +
                " sum(twelve) as twelve,db.base_year,db.sa_id,bc.o_name,db.company_id from T_CRM_KPI_DB db, ");
        sql.append(" (select * from t_bc_organ where 1=1 ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" )bc ");
        sql.append(" where db.company_id=bc.id ");
        sql.append(" and db.count_tag = '到店率' ");
        if (!"".equals(yDate)) {
            sql.append(" and db.count_year = ? ");
            params.add(yDate);
        }
        if (!"".equals(sa)) {
            sql.append(" and db.sa = ? ");
            params.add(sa);
        }
        sql.append("  group by db.company_id) ");
        sql.append(" AS t WHERE 1=1 ORDER BY sa desc, count_tag DESC ");
        return this.ikpiDao.list(sql.toString(), params.toArray());

    }

    /**
     * 新增工单客户提醒
     *
     * @param mDate
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> newCarOrder(String mDate, String uid) throws Exception {
        //取公司ID
        String companyId = TokenUtil.getCompanyId(uid);

        // 取SA
        String saSQL = "select u.u_name,u.id from t_bc_user u ,t_bc_role_user ru where u.id = ru.u_id and ru.r_id = '" + SA_ROLE_ID + "' and u.o_id in (" + deptIds(companyId) + ")";
        List<Map<String, Object>> saList = this.ikpiDao.list(saSQL);
        // 增加未分配
        Map wfpMap = new HashMap();
        wfpMap.put("uName", "未分配");
        wfpMap.put("id", "wfp");
        saList.add(0, wfpMap);
        //取SA对应的车辆数
        String carSQL = "SELECT d.u_id,count(o.vin) AS count FROM t_crm_car o LEFT JOIN t_crm_car_conf_detail d ON o.vin=d.vin AND o.company_id=d.company_id AND d.role_id='" + SA_ROLE_ID + "' WHERE from_unixtime(o.create_time/1000,'%Y-%m-%d')=? and o.company_id =? GROUP BY d.u_id ";
        List carParam = new ArrayList();
        carParam.add(mDate);
        carParam.add(companyId);
        List<Map<String, Object>> carList = this.ikpiDao.list(carSQL, carParam.toArray());
        Map carMap = new HashMap();
        for (Map temp : carList) {
            String carUid = String.valueOf(temp.get("uId"));
            if (carUid.toLowerCase().equals("null")) {
                carUid = "wfp";
            }
            carMap.put(carUid, temp.get("count"));
        }

        //取SA对应的工单数
        String orderSQL = "SELECT d.u_id,count(o.order_num) AS count from t_crm_car_workorder o LEFT JOIN t_crm_car_conf_detail d ON o.vin=d.vin AND o.company_id=d.company_id AND d.role_id='" + SA_ROLE_ID + "' WHERE from_unixtime(o.create_time/1000,'%Y-%m-%d')=? AND o.company_id=? GROUP BY d.u_id";
        List<Map<String, Object>> orderList = this.ikpiDao.list(orderSQL, carParam.toArray());
        //返回拼接的结果
        Map orderMap = new HashMap();
        for (Map temp : orderList) {
            String carUid = String.valueOf(temp.get("uId"));
            if (carUid.toLowerCase().equals("null")) {
                carUid = "wfp";
            }
            orderMap.put(carUid, temp.get("count"));
        }
        boolean isManger = TokenUtil.isCompanyAdmin(uid);

        List<Map<String, Object>> resList = new ArrayList<>();
        String saId;
        for (Map userMap : saList) {
            saId = String.valueOf(userMap.get("id"));
            userMap.put("SA", userMap.get("uName"));
            userMap.put("carCount", carMap.get(saId));
            userMap.put("woCount", orderMap.get(saId));
            userMap.put("impDate", mDate);
            if (isManger) {
                resList.add(userMap);
            } else {
                if (saId.equals(uid)) {
                    resList.add(userMap);
                }
            }
        }

        return resList;
    }


    private String deptIds(String companyId) throws Exception {
        //取下一级目录
        String sql = "select id from t_bc_organ where o_p_id = ? ";
        List<Map<String, Object>> ids = this.ikpiDao.list(sql, companyId);
        String IDStr = "";
        for (Map mp : ids) {
            IDStr += ",'" + String.valueOf(mp.get("id")) + "'";
        }

        return IDStr.substring(1);
    }

    /**
     * 产值分析报表,取已时间段每日产值数据
     *
     * @param uid
     * @param data 参数集合
     * @return
     * @throws Exception
     */
    @Override
    public List balaceAnalysis(String uid, Map data) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        String companyId = StringUtil.formatEmpty(data.get("companyId"));
        String area = StringUtil.formatEmpty(data.get("area"));
        String brand = StringUtil.formatEmpty(data.get("brand"));
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        sqlBuf.append(" select round(sum(if(w.real_amount is null or w.real_amount ='',0,w.real_amount )),2) valus," +
                "FROM_UNIXTIME(w.balance_date/1000,'%Y-%m-%d') 'key' ")
                .append(" from ( select * from t_crm_car_workorder where 1=1 ");

        if (!data.get("wxType").toString().isEmpty()) {
            sqlBuf.append("and wx_type = ? ");
            params.add(data.get("wxType").toString());
        }
        if (data.get("startDate") != null && !StringUtils.isEmpty(data.get("startDate"))) {
            sqlBuf.append(" and FROM_UNIXTIME(balance_date/1000,'%Y-%m-%d') >= ? ");
            params.add(data.get("startDate"));
        }
        if (data.get("stopDate") != null && !StringUtils.isEmpty(data.get("stopDate"))) {
            sqlBuf.append(" and FROM_UNIXTIME(balance_date/1000,'%Y-%m-%d') <= ? ");
            params.add(data.get("stopDate"));
        }


        sqlBuf.append(" ) w, ");
        sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");


        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
        sqlBuf.append(" ) bc ");
        sqlBuf.append(" where bc.id=w.company_id ");

        sqlBuf.append(" group by FROM_UNIXTIME(w.balance_date/1000,'%Y-%m-%d'),w.company_id ");
        return this.ikpiDao.list(sqlBuf.toString(), params.toArray());
    }

    /**
     * @Name:进厂辆次实现类
     * @Date: 2017/8/21 16:02
     * @Author: Liu XiaoLong
     */
    @Override
    public List enterVehicleNum(String uid, Map data) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        String companyId = StringUtil.formatEmpty(data.get("companyId"));
        String area = StringUtil.formatEmpty(data.get("area"));
        String brand = StringUtil.formatEmpty(data.get("brand"));
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        String start = StringUtil.formatEmpty(data.get("start"));
        String stop = StringUtil.formatEmpty(data.get("stop"));
        sqlBuf.append("SELECT COUNT(DISTINCT w.vin) number,FROM_UNIXTIME( ");
        sqlBuf.append("w.balance_date / 1000,'%Y-%m-%d') date FROM ( SELECT * FROM t_crm_car_workorder WHERE 1=1 ");
        if (!data.get("wxType").toString().isEmpty()) {
            sqlBuf.append("and wx_type = ? ");
            params.add(data.get("wxType").toString());
        }
        if (!start.isEmpty()) {
            sqlBuf.append("and FROM_UNIXTIME( balance_date / 1000, '%Y-%m-%d')>=? ");
            params.add(start);
        }
        if (!stop.isEmpty()) {
            sqlBuf.append("and FROM_UNIXTIME( balance_date / 1000, '%Y-%m-%d')<=? ");
            params.add(stop);
        }

        sqlBuf.append(" ) w ");
        sqlBuf.append(" , ");
        sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
        sqlBuf.append(" ) bc ");
        sqlBuf.append(" where bc.id=w.company_id ");

        sqlBuf.append("GROUP BY FROM_UNIXTIME( w.balance_date / 1000, '%Y-%m-%d'),w.company_id ");
        sqlBuf.append("ORDER BY FROM_UNIXTIME( w.balance_date / 1000, '%Y-%m-%d' )");

        return this.ikpiDao.list(sqlBuf.toString(), params.toArray());
    }


    /**
     * 新增基盘趋势
     * 目前只算了新车增长
     *
     * @param uid
     * @param DateYearMonth
     * @return
     * @throws Exception
     */
    @Override
    public List newAddBaseData(String uid, String DateYearMonth, String companyId, String area, String brand) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        if (StringUtils.isEmpty(DateYearMonth)) {
            Long todayL = DateUtils.getNowTime();
            DateYearMonth = DateUtils.formatLongTime(todayL, "YYYY-MM");
        }
        String oid = TokenUtil.getCompanyId(uid);
        boolean isAdmin = TokenUtil.isCompanyAdmin(uid);
        sqlBuf.append("SELECT  COUNT(DISTINCT tcc.vin) as total,detail.tag_date addDate,detail.company_id  FROM " +
                " t_crm_car_base_detail detail,  ( " +
                " SELECT  detail.*,bc.o_name  FROM " +
                " t_crm_car_conf_detail detail ");
        sqlBuf.append(" , ");
        sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");

        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
        sqlBuf.append(" ) bc ");
        sqlBuf.append(" WHERE detail.company_id = bc.id " +
                " AND detail.role_id = '" + Constant.SA_ROLE_ID + "'");
        if (!isAdmin) {
            sqlBuf.append(" AND detail.u_id=? ");
            params.add(uid);
        }
        sqlBuf.append(" ) tcc " +
                " WHERE " +
                " tcc.vin = detail.vin " +
                " AND tcc.company_id = detail.company_id " +
//                " AND detail.company_id = ? " +
                " AND DATE_FORMAT(detail.tag_date,'%Y-%m') = ? " +
                " AND detail.base_type = '1'");
        sqlBuf.append(" GROUP BY detail.tag_date,detail.company_id ");
        params.add(DateYearMonth);
        return this.ikpiDao.list(sqlBuf.toString(), params.toArray());
    }

    /**
     * 新增基盘趋势明细
     *
     * @param uid
     * @param DateYearMonthDay
     * @return
     * @throws Exception
     */
    @Override
    public Map newAddBaseDataDetail(String uid, String DateYearMonthDay, String start, String limit, String companyId) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        boolean isAdmin = TokenUtil.isCompanyAdmin(uid);

        sqlBuf.append("select * from (");
        sqlBuf.append("SELECT  tcc.*, detail.tag_date  FROM " +
                " t_crm_car_base_detail detail,  ( " +
                " SELECT  car.*  FROM " +
                " t_crm_car car, " +
                " t_crm_car_conf_detail detail " +
                " WHERE " +
                " car.company_id = detail.company_id " +
                " AND car.vin = detail.vin " +
                " AND detail.role_id = '" + Constant.SA_ROLE_ID + "' " +
                " AND detail.company_id = ? ");
        if (!isAdmin) {
            sqlBuf.append(" AND detail.u_id=? ");
            params.add(uid);
        }
        sqlBuf.append(" ) tcc " +
                " WHERE " +
                " tcc.id = detail.car_id " +
                " AND tcc.company_id = detail.company_id " +
                " AND detail.company_id = ? " +
                " AND detail.tag_date = ? " +
                " AND detail.base_type = '1'");
        sqlBuf.append("GROUP BY vin ) temp ");
        params.add(companyId);
        params.add(companyId);
        params.add(DateYearMonthDay);
//        sqlBuf.append(" SELECT tcc.*,detail.tag_date from t_crm_car_base_detail detail  ")
//                .append(" LEFT JOIN t_crm_car tcc on detail.car_id= tcc.id ")
//                .append(" where  detail.vin in (SELECT vin from t_crm_car_conf_detail  ")
//                .append(" where role_id = '"+Constant.SA_ROLE_ID+"') ")
//                .append("  and tag_date =? and base_type='1' ");
//        params.add(DateYearMonthDay);
//        if (!isAdmin) {
//            sqlBuf.append(" and detail.company_id = ? ");
//            params.add(oid);
//        }
        return this.ikpiDao.page(sqlBuf.toString(), params.toArray(), start, limit);
    }

    /**
     * @Name: 基盘车辆贡献分布
     * @Author: Liu XiaoLong
     * @Date: 2017/8/31 15:28
     */
    @Override
    public Map baseCarContribution(String uid, Map map, String start, String limit) throws Exception {
        List params = new ArrayList();
        String sql = "SELECT * FROM (SELECT base.company_id, base.o_area, base.o_name, base.o_car_brand brand, COUNT( IF ( base.realAmount >= 0 AND base.realAmount < 2000, 1, NULL )) count1 "
                + ", COUNT( IF ( base.realAmount >= 2000 AND base.realAmount < 3000, 1, NULL )) count2 "
                + ", COUNT( IF ( base.realAmount >= 3000 AND base.realAmount < 5000, 1, NULL )) count3 "
                + ", COUNT( IF ( base.realAmount >= 5000 AND base.realAmount < 8000, 1, NULL )) count4 "
                + ", COUNT( IF ( base.realAmount >= 8000 AND base.realAmount < 10000, 1, NULL )) count5 "
                + ", COUNT( IF ( base.realAmount >= 10000, 1, NULL )) count6, COUNT(IF(base.vin IS NULL, 1, 1)) sum FROM ( "
                + "SELECT a.vin, SUM(a.real_amount) realAmount, a.company_id, b.o_area, b.o_name, b.o_car_brand,c.active FROM t_crm_car_workorder a  "
                + "LEFT JOIN t_bc_organ b ON a.company_id = b.id LEFT JOIN t_crm_car c ON a.vin = c.vin WHERE a.company_id = c.company_id  ";
        if (!StringUtils.isEmpty(map.get("companyId"))) { // 公司过滤
            sql = sql + "AND a.company_id = ? ";
            params.add(map.get("companyId"));
        }
        /*if(!StringUtils.isEmpty(map.get("brand"))){ // 品牌过滤
            sql = sql + "AND b.o_car_brand=? ";
            params.add(map.get("brand"));
        }*/
        if (!StringUtils.isEmpty(map.get("active"))) { // 活跃过滤
            sql = sql + "AND c.active=? ";
            params.add(map.get("active"));
        }
       /* if(!StringUtils.isEmpty(map.get("area"))){ // 区域过滤
            sql = sql + "AND base.o_area=? ";
            params.add(map.get("area"));
        }*/
        if (map.get("type").equals("base")) { // 基盘、车辆过滤
            sql = sql + "AND a.vin IN ( SELECT vin FROM ( SELECT vin FROM t_crm_car_workorder WHERE company_id = ? AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 ) AS x) ";
            params.add(map.get("companyId"));
        }
        sql = sql + "GROUP BY a.vin) base ) t ";


        // sql = sql + " ";
        return this.ikpiDao.page(sql, params.toArray(), start, limit);
    }

    /**
     * @Name: 基盘车辆贡献 【详情】
     * @Author: Liu XiaoLong
     * @Date: 2017/9/6 09:28
     */
    @Override
    public Map baseCarContributionDetail(String uid, String start, String limit, String companyId, Map data) throws Exception {
        if (!StringUtils.isEmpty(companyId)) {
            List params = new ArrayList();
            String sql = "SELECT * FROM (SELECT a.vin, a.company_id, b.o_area, b.o_name, c.brand, c.plate_num, c.series, c.model, c.car_age, c.owner_city, c.owner_name ";
            sql = sql + ", c.from_company, c.buy_date, c.prev_maintain_date, c.active FROM t_crm_car_workorder a LEFT JOIN t_bc_organ b ON a.company_id = b.id ";
            sql = sql + "LEFT JOIN t_crm_car c ON a.vin = c.vin AND a.company_id = c.company_id WHERE  a.company_id =? ";
            params.add(companyId);
            // sql = sql + ") base LEFT JOIN t_crm_car a ON base.vin = a.vin AND base.company_id = a.company_id ) temp WHERE 1=1 ";
            if (!StringUtils.isEmpty(data.get("brand"))) {
                sql = sql + "AND b.o_car_brand = ? ";
                params.add(data.get("brand"));
            }
            if (!StringUtils.isEmpty(data.get("active"))) {
                sql = sql + "AND c.active = ? ";
                params.add(data.get("active"));
            }
            if (data.get("type").equals("base")) { // 基盘、车辆过滤
                sql = sql + "AND a.vin IN ( SELECT DISTINCT (vin) FROM t_crm_car_workorder WHERE company_id = ? AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 ) ";
                params.add(companyId);
            }
            sql = sql + "GROUP BY a.vin ) temp ";
            return this.ikpiDao.page(sql, params.toArray(), start, limit);
        }

        return null;
    }


    /**
     * @Name: 基盘车辆车系分布
     * @Author: Liu XiaoLong
     * @Date: 2017/8/28 17:37
     */
    @Override
    public Map baseCarSerice(String uid, Map map, String start, String limit) throws Exception {


        if (!StringUtils.isEmpty(map.get("companyId"))) { // 公司ID过滤
            StringBuffer sql = new StringBuffer();
            List params = new ArrayList();
            sql.append("SELECT * FROM (SELECT base.company_id, base.series, a.allNumber, b.o_area,b.o_car_brand brand, b.o_name, COUNT(1) seriesNumber FROM ");
            sql.append("( SELECT id,vin, company_id, active, IF(series IS NULL OR series = '','未知',series) series, brand FROM t_crm_car WHERE company_id =? ) base ");
            params.add(map.get("companyId"));
            sql.append("LEFT JOIN ( SELECT COUNT(id) allNumber, company_id FROM t_crm_car WHERE company_id = ? ");
            params.add(map.get("companyId"));

            /*if(!StringUtils.isEmpty(map.get("brand"))){ // 品牌过滤
                sql.append("AND brand = ? ");
                params.add(map.get("brand"));
            }*/
            if (!StringUtils.isEmpty(map.get("active"))) { // 活跃过滤
                sql.append("AND active = ? ");
                params.add(map.get("active"));
            }
            if (map.get("type").equals("base")) { // 基盘、车辆过滤
                sql.append("AND vin IN ( SELECT DISTINCT (vin) FROM t_crm_car_workorder WHERE company_id = ? AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 ) ");
                params.add(map.get("companyId"));
            }
            sql.append(") a ON a.company_id = base.company_id LEFT JOIN ( SELECT o_area,o_car_brand, o_name, id FROM t_bc_organ WHERE id = ?  ) b ON b.id = base.company_id WHERE 1=1 ");
            params.add(map.get("companyId"));
            /*if(!StringUtils.isEmpty(map.get("brand"))){ // 品牌过滤
                sql.append("AND base.brand = ? ");
                params.add(map.get("brand"));
            }*/
            if (!StringUtils.isEmpty(map.get("active"))) { // 活跃过滤
                sql.append("AND base.active = ? ");
                params.add(map.get("active"));
            }
            if (map.get("type").equals("base")) { // 基盘、车辆过滤
                sql.append("AND base.vin IN ( SELECT DISTINCT (vin) FROM t_crm_car_workorder WHERE company_id = ? AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 ) ");
                params.add(map.get("companyId"));
            }
            sql.append("GROUP BY base.series) temp ");

            return this.ikpiDao.page(sql.toString(), params.toArray(), start, limit);
        }
        return null;
    }

    /**
     * @Name: 基盘车辆车系分布 【详情】
     * @Author: Liu XiaoLong
     * @Date: 2017/9/6 14:39
     * @Remark:
     */
    @Override
    public Map baseCarSericeDetail(String uid, String start, String limit, String companyId, Map data) throws Exception {
        if (!StringUtils.isEmpty(companyId)) {
            StringBuffer sql = new StringBuffer();
            List params = new ArrayList();
            sql.append("SELECT base.company_id, base.series, base.brand, base.vin, base.plate_num, base.model, base.car_age, base.owner_name, base.owner_city,base.active ");
            sql.append(", base.from_company, base.buy_date, base.prev_maintain_date, b.o_area, b.o_name FROM ( SELECT id, company_id, active, series, brand ");
            sql.append(", vin, plate_num, model, car_age, owner_name, owner_city, from_company, buy_date, prev_maintain_date FROM t_crm_car WHERE company_id = ? ");
            params.add(companyId);

            sql.append(") base LEFT JOIN ( SELECT o_area, o_name, id FROM t_bc_organ WHERE id = ? ) b ON b.id = base.company_id WHERE 1=1 ");
            params.add(companyId);
            /*if(!StringUtils.isEmpty(data.get("brand"))){ // 品牌过滤
                sql.append("AND brand = ? ");
                params.add(data.get("brand"));
            }*/
            if (!StringUtils.isEmpty(data.get("active"))) { // 活跃过滤
                sql.append("AND active = ? ");
                params.add(data.get("active"));
            }
            if (!StringUtils.isEmpty(data.get("series"))) { // 车系过滤
                sql.append("AND series = ? ");
                params.add(data.get("series"));
            }
            if (data.get("type").equals("base")) { // 基盘、车辆过滤
                sql.append("AND vin IN (SELECT DISTINCT(vin) FROM t_crm_car_base_detail) ");
            }
            return this.ikpiDao.page(sql.toString(), params.toArray(), start, limit);
        }
        return null;
    }


    /**
     * @Name: 基盘车辆消费频次
     * @Author: Liu XiaoLong
     * @Date: 2017/9/4 10:38
     */
    @Override
    public Map baseCarConsumption(String uid, Map map, String start, String limit) throws Exception {
        if (!StringUtils.isEmpty(map.get("companyId"))) {
            List params = new ArrayList();
            String sql = "SELECT * FROM (SELECT b2.o_car_brand brand, b2.company_id,b2.o_area,b2.o_name, COUNT(IF(b2.countmj = 1, 1, NULL)) AS count1, COUNT(IF(b2.countmj = 2, 1, NULL)) AS count2, COUNT(IF(b2.countmj = 3, 1, NULL)) AS count3, "
                    + "COUNT(IF(b2.countmj = 4, 1, NULL)) AS count4, COUNT(IF(b2.countmj = 5, 1, NULL)) AS count5, COUNT(b2.vin) AS countall, SUM( IF ( b2.countmj = 1, receive_amount, NULL )) AS sum1, "
                    + "SUM( IF ( b2.countmj = 2, receive_amount, NULL )) AS sum2, SUM( IF ( b2.countmj = 3, receive_amount, NULL )) AS sum3, SUM( IF ( b2.countmj = 4, receive_amount, NULL )) AS sum4, "
                    + "SUM( IF ( b2.countmj = 5, receive_amount, NULL )) AS sum5 FROM ( SELECT base.brand,base.o_car_brand, base.company_id, base.receive_amount, base.vin,base.o_area,base.o_name, "
                    + "( CASE WHEN COUNT(base.vin) = 1 THEN 1 WHEN COUNT(base.vin) = 2 THEN 2 WHEN COUNT(base.vin) = 3 THEN 3 WHEN COUNT(base.vin) = 4 THEN 4 WHEN COUNT(base.vin) >= 5 THEN 5 END ) AS countmj FROM ( " // vin  公司关联
                    + "SELECT a.balance_date date, a.brand, b.active,c.o_area,c.o_name,c.o_car_brand, a.company_id, a.vin, a.receive_amount FROM t_crm_car_workorder a LEFT JOIN t_crm_car b ON a.vin = b.vin AND a.company_id = b.company_id LEFT JOIN t_bc_organ c ON c.id = a.company_id WHERE  a.company_id =? ";
            params.add(map.get("companyId"));
            if (map.get("type").equals("base")) {
                sql = sql + "AND b.vin IN ( SELECT vin FROM ( SELECT vin FROM t_crm_car_workorder WHERE company_id = ? ";
                sql = sql + "AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR)) * 1000 ) a ) ";
                params.add(map.get("companyId"));
            }

            sql = sql + ") base WHERE 1=1 ";

            if (!StringUtils.isEmpty(map.get("start"))) {
                sql = sql + "AND base.date >= ? ";
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse((String) map.get("start"));
                long ts = date.getTime();
                params.add(ts);
            }
            if (!StringUtils.isEmpty(map.get("stop"))) {
                sql = sql + "AND base.date <= ? ";
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse((String) map.get("stop"));
                long ts = date.getTime();
                params.add(ts);
            }
                /*if(!StringUtils.isEmpty(map.get("area"))){
                    sql =sql + "AND base.o_area =? ";
                    params.add(map.get("area"));
                }*/
        /*if(!StringUtils.isEmpty(map.get("brand"))){
            sql =sql + "AND base.brand =? ";
            params.add(map.get("brand"));
        }*/
            if (!StringUtils.isEmpty(map.get("active"))) {
                sql = sql + "AND base.active =? ";
                params.add(map.get("active"));
            }
       /* if(map.get("type").equals("base")){
            sql =sql + "AND base.vin IN ( SELECT DISTINCT (vin) FROM t_crm_car_workorder WHERE company_id = ? AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 ) ";
            params.add(map.get("companyId"));
        }*/
            sql = sql + "GROUP BY base.vin ) b2 ";
        /*if(!StringUtils.isEmpty(map.get("area")) && StringUtils.isEmpty(map.get("companyId"))){
            sql =sql + "GROUP BY b2.company_id ";
        }*/
            sql = sql + ") b1 ";

            return this.ikpiDao.page(sql, params.toArray(), start, limit);
        }
        return null;

    }


    /**
     * @Name: 基盘车辆车龄分布
     * @Author: Liu XiaoLong
     * @Date: 2017/8/30 20:24
     */
    @Override
    public Map baseCarAge(String uid, Map map, String start, String limit) throws Exception {
        //StringBuffer sql  =  new StringBuffer();
        if (!StringUtils.isEmpty(map.get("companyId"))) {
            List params = new ArrayList();
            String sql = "SELECT * FROM ( SELECT *,COUNT(IF(a.car_age = 1, 1, NULL)) count1, COUNT(IF(a.car_age = 2, 1, NULL)) count2, COUNT(IF(a.car_age = 3, 1, NULL)) count3 ";
            sql = sql + ", COUNT(IF(a.car_age = 4, 1, NULL)) count4, COUNT(IF(a.car_age = 5, 1, NULL)) count5, COUNT(IF(a.car_age = 6, 1, NULL)) count6 ";
            sql = sql + ", COUNT(IF(a.car_age = 7, 1, NULL)) count7, COUNT(IF(a.car_age >= 8, 1, NULL)) count8, COUNT( IF ( a.car_age IS NULL OR a.car_age <= 0, 1, NULL )) count0 ";
            sql = sql + ", COUNT(IF(a.vin IS NULL, 1, 1)) sum FROM ( SELECT a.vin, a.active, a.company_id, a.car_age, c.o_area,c.o_car_brand brand, c.o_name FROM ( ";
            sql = sql + "SELECT active, company_id, brand, car_age, vin FROM t_crm_car WHERE company_id =? GROUP BY vin ) a LEFT JOIN t_bc_organ c ON c.id = a.company_id ) a WHERE 1=1 ";
            params.add(map.get("companyId"));

            /*if(!StringUtils.isEmpty(map.get("area"))){
                sql = sql + "AND a.o_area = ? ";
                params.add(map.get("area"));
            }*/
            if (!StringUtils.isEmpty(map.get("active"))) {
                sql = sql + "AND a.active = ? ";
                params.add(map.get("active"));
            }
            /*if(!StringUtils.isEmpty(map.get("brand"))){
                sql = sql + "AND a.brand = ? ";
                params.add(map.get("brand"));
            }*/
            if (map.get("type").equals("base")) {
                sql = sql + "AND a.vin IN ( SELECT DISTINCT (vin) FROM t_crm_car_workorder WHERE company_id = ? AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 ) ";
                params.add(map.get("companyId"));
            }
            sql = sql + ") temp ";
            return this.ikpiDao.page(sql, params.toArray(), start, limit);
        }
        return null;
    }

    /**
     * @Name: 基盘车辆车龄分布 【详情】
     * @Author: Liu XiaoLong
     * @Date: 2017/9/5 19:14
     */
    @Override
    public Map baseCarAgeDetail(String uid, String start, String limit, String companyId, Map data) throws Exception {

        if (!StringUtils.isEmpty(companyId)) {
            List params = new ArrayList();
            String sql = "SELECT * FROM ( SELECT a.vin, a.active, a.company_id,a.plate_num, a.series, a.model, a.owner_name, a.owner_city ";
            sql = sql + ", a.from_company, a.car_age, a.buyDate, a.prevMaintainDate, a.brand, c.o_area, c.o_name FROM ( SELECT active, company_id, brand ";
            sql = sql + ", car_age, plate_num, series, model, owner_name, owner_city, from_company, FROM_UNIXTIME(buy_date / 1000, '%Y-%m-%d') buyDate ";
            sql = sql + ", FROM_UNIXTIME( prev_maintain_date / 1000, '%Y-%m-%d' ) prevMaintainDate, vin FROM t_crm_car WHERE company_id=? GROUP BY vin ) a ";
            params.add(companyId);
            sql = sql + "LEFT JOIN t_bc_organ c ON c.id = a.company_id ) a WHERE 1 = 1 ";

            /*if(!StringUtils.isEmpty(data.get("brand"))){
                sql = sql + "AND a.brand = ? ";
                params.add(data.get("brand"));
            }*/
            if (!StringUtils.isEmpty(data.get("active"))) {
                sql = sql + "AND a.active = ? ";
                params.add(data.get("active"));
            }
            /*if(!StringUtils.isEmpty(data.get("area"))){
                sql = sql + "AND a.o_area = ? ";
                params.add(data.get("area"));
            }*/
            if (data.get("type").equals("base")) { // 基盘、车辆过滤
                sql = sql + "AND a.vin IN ( SELECT DISTINCT (vin) FROM t_crm_car_workorder WHERE company_id = ? AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 ) ";
                params.add(companyId);
            }
            return this.ikpiDao.page(sql, params.toArray(), start, limit);
        }

        return null;
    }


    /**
     * @Name: 基盘消费频次 【详情】
     * @Author: Liu XiaoLong
     * @Date: 2017/9/5 09:01
     */
    @Override
    public Map baseCarDetail(String uid, String start, String limit, String companyId, Map data) throws Exception {
        List params = new ArrayList();
        if (!StringUtils.isEmpty(companyId)) {
            String sql = "SELECT * FROM (SELECT *, COUNT(base.vin) number, SUM(base.receive_amount) cost FROM ( ";
            sql = sql + "SELECT * FROM ( SELECT b.prev_maintain_date lastBack,a.balance_date date, a.brand,b.active, ";
            sql = sql + "b.model, b.series, b.car_age, b.owner_name, b.owner_city, b.from_company, b.buy_date,b.plate_num,c.o_area, ";
            sql = sql + "c.o_name,a.company_id,a.vin,a.receive_amount FROM t_crm_car_workorder a LEFT JOIN ";
            sql = sql + "t_crm_car b ON a.vin = b.vin AND a.company_id = b.company_id LEFT JOIN t_bc_organ c ON c.id = a.company_id WHERE a.company_id = ? ";
            // sql = sql + "AND base.company_id =? ";
            params.add(companyId);

            if (data.get("type").equals("base")) { // 基盘、车辆过滤
                sql = sql + "AND b.vin IN ( SELECT vin FROM ( SELECT vin FROM t_crm_car_workorder WHERE company_id = ? ";
                params.add(companyId);
                sql = sql + "AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR)) * 1000 ) a ) ";
            }
            sql = sql + ") a WHERE 1=1 ";
            /*if(!StringUtils.isEmpty(data.get("brand"))){
                sql = sql + "AND a.brand = ? ";
                params.add(data.get("brand"));
            }*/
            if (!StringUtils.isEmpty(data.get("active"))) {
                sql = sql + "AND a.active = ? ";
                params.add(data.get("active"));
            }
            if (!StringUtils.isEmpty(data.get("start"))) {
                sql = sql + "AND a.date >=? ";
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse((String) data.get("start"));
                long ts = date.getTime();
                params.add(ts);
            }
            if (!StringUtils.isEmpty(data.get("stop"))) {
                sql = sql + "AND a.date <=? ";
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse((String) data.get("stop"));
                long ts = date.getTime();
                params.add(ts);
            }
            /*if(!StringUtils.isEmpty(data.get("area"))){
                sql = sql + "AND a.o_area = ? ";
                params.add(data.get("area"));
            }*/
            /*if(data.get("type").equals("base")) { // 基盘、车辆过滤
                sql = sql + "AND a.vin IN ( SELECT DISTINCT (vin) FROM t_crm_car_workorder WHERE company_id = ? AND real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 ) ";
                params.add(companyId);
            }*/
            sql = sql + ") base GROUP BY base.vin)b1";
            return this.ikpiDao.page(sql, params.toArray(), start, limit);
        }
        return null;
    }

    /**
     * 流失基盘趋势
     *
     * @param uid
     * @param DateYearMonth
     * @return
     * @throws Exception
     */
    @Override
    public List lostBaseData(String uid, String DateYearMonth, String companyId, String area, String brand) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        if (StringUtils.isEmpty(DateYearMonth)) {
            Long todayL = DateUtils.getNowTime();
            DateYearMonth = DateUtils.formatLongTime(todayL, "YYYY-MM");
        }
        String oid = TokenUtil.getCompanyId(uid);
        boolean isAdmin = TokenUtil.isCompanyAdmin(uid);
        sqlBuf.append("SELECT COUNT(DISTINCT tcc.vin) as total,detail.start_date lostDate,detail.company_id FROM t_crm_car_lost_detail detail," +
                " (SELECT detail.*,bc.o_name FROM t_crm_car_conf_detail detail ");
        sqlBuf.append(" , ");
        sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");

        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
        sqlBuf.append(" ) bc ");
        sqlBuf.append(" WHERE detail.company_id = bc.id " +
                " AND detail.role_id = '" + Constant.SA_ROLE_ID + "'");
        if (!isAdmin) {
            sqlBuf.append(" AND detail.u_id=? ");
            params.add(uid);
        }
        sqlBuf.append(
                " ) tcc" +
                        " WHERE detail.vin = tcc.vin " +
                        " AND detail.company_id = tcc.company_id" +
                        " AND DATE_FORMAT(detail.start_date,'%Y-%m') = ?");
//                " GROUP BY tcc.id");
        params.add(DateYearMonth);
        sqlBuf.append(" GROUP BY detail.start_date,detail.company_id ");
        return this.ikpiDao.list(sqlBuf.toString(), params.toArray());
    }


    /**
     * 流失基盘趋势明细
     *
     * @param uid
     * @param DateYearMonthDay
     * @return
     * @throws Exception
     */
    @Override
    public Map lostBaseDataDetail(String uid, String DateYearMonthDay, String start, String limit, String companyId) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        boolean isAdmin = TokenUtil.isCompanyAdmin(uid);
        sqlBuf.append("select * from  (");
        sqlBuf.append("SELECT tcc.*, detail.start_date FROM t_crm_car_lost_detail detail," +
                " (SELECT car.* FROM" +
                " t_crm_car car, t_crm_car_conf_detail detail" +
                " WHERE car.vin = detail.vin AND car.company_id = detail.company_id" +
                " AND detail.role_id = '" + Constant.SA_ROLE_ID + "'");
        if (!isAdmin) {
            sqlBuf.append(" AND detail.u_id=? ");
            params.add(uid);
        }
        sqlBuf.append(" AND detail.company_id=?" +
                " ) tcc" +
                " WHERE detail.car_id = tcc.id " +
                " AND detail.company_id = tcc.company_id" +
                " AND detail.start_date = ?" +
                " and detail.company_id=? " +
                " GROUP BY tcc.id");
        sqlBuf.append(" ) temp ");
        params.add(companyId);
        params.add(DateYearMonthDay);
        params.add(companyId);
        return this.ikpiDao.page(sqlBuf.toString(), params.toArray(), start, limit);
    }


    public Boolean appendWhereIfNeed(StringBuffer sql, Boolean hasWhere) {

        if (hasWhere == false) {

            sql.append(" where ");

        } else {

            sql.append(" and ");

        }
        return true;

    }


    /*
     * @Name: 流失车辆表
     * @Author: Liu XiaoLong
     * @Date: 2017/9/10 22:31
     * @Remark:
     */
    @Override
    public Map lostCarTable(String uid, Map map, String start, String limit) throws Exception {

        if (!StringUtils.isEmpty(map.get("companyId"))) {
            List params = new ArrayList();
            String sql = "SELECT * FROM ( SELECT DATE_FORMAT(base.start_date, '%Y-%m') monthmj, COUNT(1) total, base.company_id FROM ( "
                    + "SELECT a.vin, b.id, c.start_date, c.end_date, c.company_id FROM t_crm_car_conf_detail a "
                    + ", t_crm_car b, t_crm_car_lost_detail c WHERE a.company_id = ? ";
            params.add(map.get("companyId"));
            if (!StringUtils.isEmpty(map.get("people"))) {
                sql = sql + "AND a.u_id = ? ";
                params.add(map.get("people"));
            }

            sql = sql + "AND a.vin = b.vin AND a.company_id = b.company_id AND c.car_id = b.id ) base GROUP BY monthmj ) a WHERE 1 = 1 ";
            if (!StringUtils.isEmpty(map.get("start"))) {
                sql = sql + " AND a.monthmj >= ? ";
                params.add(map.get("start"));
            }
            if (!StringUtils.isEmpty(map.get("stop"))) {
                sql = sql + " AND a.monthmj <= ? ";
                params.add(map.get("stop"));
            }
            return this.ikpiDao.page(sql, params.toArray(), start, limit);
        }
        return null;

    }

    /**
     * @Name: 流失车辆表详情
     * @Author: Liu XiaoLong
     * @Date: 2017/9/10 23:14
     * @Remark:
     */
    @Override
    public Map lostCarTableDetail(String uid, Map map, String start, String stop) throws Exception {
        if (!StringUtils.isEmpty(map.get("companyId"))) {
            List params = new ArrayList();
            String sql = "SELECT base.*, b.brand, c.o_area, c.o_name, b.plate_num, b.vin, b.owner_name, b.owner_phone,d.u_name FROM  ";
            sql = sql + "( SELECT a.vin,a.u_id, b.id, c.start_date, c.end_date, c.company_id, c.car_id FROM t_crm_car_conf_detail a, t_crm_car b ";
            sql = sql + ", t_crm_car_lost_detail c WHERE a.company_id = ? ";

            params.add(map.get("companyId"));
            if (!StringUtils.isEmpty(map.get("people"))) {
                sql = sql + "AND a.u_id = ? ";
                params.add(map.get("people"));

            }
            if (!StringUtils.isEmpty(map.get("date"))) {
                sql = sql + "AND DATE_FORMAT(c.start_date, '%Y-%m') = ? ";
                params.add(map.get("date"));
            }
            sql = sql + "AND a.vin = b.vin AND a.company_id = b.company_id AND c.car_id = b.id ) base LEFT JOIN t_crm_car b ON base.car_id = b.id ";
            sql = sql + "LEFT JOIN t_bc_organ c ON base.company_id = c.id LEFT JOIN t_bc_user d ON base.u_id = d.id ";
            return this.ikpiDao.page(sql, params.toArray(), start, stop);
        }
        return null;
    }

    /**
     * @name 保险任务过程管理 侯念晨
     * @param uid
     * @param token
     * @param
     * @return
     * @throws Exception
     */
    @Override
    public Map insuranceTsakQuery(String uid,String token, String area, String brand, String companyId, String month,
                                  String start, String limit) throws Exception{
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        //本月任务
        sql.append("select a.month, a.sa_id, a.u_name, a.o_name, a.company_id, " +
                " a.cnjMonth, a.cnsMonth, a.xbjMonth, a.xbsMonth, a.hytMonth, a.fhytMonth, a.jdjMonth, a.jdsMonth, a.totalMonth, " +
                " a.cnjMonthDone, a.cnsMonthDone, a.xbjMonthDone, a.xbsMonthDone, a.hytMonthDone, a.fhytMonthDone, " +
                " a.jdjMonthDone, a.jdsMonthDone, a.totalMonthDone, b.totalDay, b.doneDay, b.closeDay, a.overdueTask " +
                " from (select ? month, tr.sa_id, tr.u_name,bc.o_name,tr.company_id ," +
                " count(if(tr.expect_date < DATE_FORMAT(now(),'%Y-%m-%d') and tr.type_pcode = 'bx'" +
                " and tr.status = '0' ,true,null)) overdueTask," +
                /** 本月任务数 **/
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'cnj',true,null)) cnjMonth ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'cns',true,null)) cnsMonth ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'xbj',true,null)) xbjMonth ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'xbs',true,null)) xbsMonth ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'hyt',true,null)) hytMonth ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'fhyt',true,null)) fhytMonth ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'jdj',true,null)) jdjMonth ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'jds',true,null)) jdsMonth ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_pcode = 'bx',true,null)) totalMonth ," +
                /** 本月已完成任务 **/
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'cnj'" +
                " and tr.status = '1' ,true,null)) cnjMonthDone ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'cns'" +
                " and tr.status = '1' ,true,null)) cnsMonthDone ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'xbj'" +
                " and tr.status = '1' ,true,null)) xbjMonthDone ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'xbs'" +
                " and tr.status = '1' ,true,null)) xbsMonthDone ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'hyt'" +
                " and tr.status = '1' ,true,null)) hytMonthDone ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'fhyt'" +
                " and tr.status = '1' ,true,null)) fhytMonthDone ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'jdj'" +
                " and tr.status = '1' ,true,null)) jdjMonthDone ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_code = 'jds'" +
                " and tr.status = '1' ,true,null)) jdsMonthDone ," +
                " count(if(left(tr.expect_date,7) = '" + month + "' and tr.type_pcode = 'bx'" +
                " and tr.status = '1' ,true,null)) totalMonthDone" +
                " from ( select * from t_bc_organ where 1=1 ");
        params.add(month);
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" ) bc,");
        sql.append(" ( SELECT tr.*, tu.u_name FROM t_crm_task_real tr" +
                " LEFT JOIN t_bc_user tu ON tr.sa_id = tu.id ) tr " +
                " where tr.company_id = bc.id and tr.type_pcode = 'bx'" +
                " AND DATE_FORMAT(tr.expect_date, '%Y-%m') = ?");
        params.add(month);
        if (!TokenUtil.isCompanyAdmin(uid)) {
            sql.append(" and tr.sa_id = ? ");
            params.add(uid);
        }
        sql.append(" group by tr.sa_id ) a ");
        //今日任务
        sql.append("left join ");
        sql.append("(select ? month, tr.sa_id, tr.u_name,bc.o_name,tr.company_id ," +
                " count(if(tr.expect_date <= DATE_FORMAT(now(),'%Y-%m-%d') and tr.type_pcode = 'bx'" +
                " and tr.status = '0' ,true,null)) totalDay ," +
                " count(if(tr.deal_date = DATE_FORMAT(now(),'%Y-%m-%d') and tr.type_pcode = 'bx'" +
                " and tr.status = '1' ,true,null)) doneDay ," +
                " count(if(tr.deal_date = DATE_FORMAT(now(),'%Y-%m-%d') and tr.type_pcode = 'bx'" +
                " and tr.status = '2' ,true,null)) closeDay " +
                " from ( select * from t_bc_organ where 1=1 ");
        params.add(month);
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
        sql.append(" ) bc,");
        sql.append(" ( SELECT tr.*, tu.u_name FROM t_crm_task_real tr" +
                " LEFT JOIN t_bc_user tu ON tr.sa_id = tu.id ) tr " +
                " where tr.company_id = bc.id and tr.type_pcode = 'bx' ");
        if (!TokenUtil.isCompanyAdmin(uid)) {
            sql.append(" and tr.sa_id = ? ");
            params.add(uid);
        }
        sql.append(" group by tr.sa_id ) b " +
                "on a.sa_id = b.sa_id ");
        Map map = this.ikpiDao.page(sql.toString(),params.toArray(),start,limit);
        return map;
    }

    /**
     * @name：保险任务过程管理明细查询
     * @Author: 侯念晨
     * @Date：2017/11/22
     * @param uid
     * @param token
     * @param companyId
     * @param saId
     * @param type 保险任务类型
     * @param time
     * @return
     * @throws Exception
     */
    public Map insuranceTaskDetail(String uid,String token,String companyId,
                                   String saId,String type,String time,String start,String limit) throws Exception{
        Map map = new HashMap();
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select * from t_crm_task_real where company_id = ? and type_pcode = 'bx' ");
        params.add(companyId);
        if (saId == null || saId.equals("")){
            sql.append("and sa_id is null ");
        }else{
            sql.append("and sa_id = ? ");
            params.add(saId);
        }

        //当天任务
        if(type.equals("totalDay")){
            sql.append("and expect_date <= DATE_FORMAT(now(),'%Y-%m-%d') and status = '0' ");
        }
        //已完成任务
        if(type.equals("doneDay")){
            sql.append("and deal_date = DATE_FORMAT(now(),'%Y-%m-%d') and status = '1' ");
        }
        //已关闭任务
        if(type.equals("closeDay")){
            sql.append("and deal_date = DATE_FORMAT(now(),'%Y-%m-%d') and status = '2' ");
        }
        //已过期任务
        if(type.equals("overdueTask")){
            sql.append("and expect_date < DATE_FORMAT(now(),'%Y-%m-%d') and status = '0' and DATE_FORMAT(expect_date, '%Y-%m') = ? ");
            params.add(time);
        }
        //本月次年交强险
        if(type.equals("cnjMonth")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'cnj' ");
        }
        //本月次年商业险
        if(type.equals("cnsMonth")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'cns' ");
        }
        //本月续保交强险
        if(type.equals("xbjMonth")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'xbj' ");
        }
        //本月续保商业险
        if(type.equals("xbsMonth")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'xbs' ");
        }
        //本月保险活跃
        if(type.equals("hytMonth")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'hyt' ");
        }
        //本月保险非活跃
        if(type.equals("fhytMonth")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'fhyt' ");
        }
        //本月交强险接待
        if(type.equals("jdjMonth")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'jdj' ");
        }
        //本月商业险接待
        if(type.equals("jdsMonth")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'jds' ");
        }
        //本月保险任务合计
        if(type.equals("totalMonth")){
            sql.append("and left(expect_date,7) = '" + time + "' ");
        }
        //本月次年交强险已完成
        if(type.equals("cnjMonthDone")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'cnj' and status = '1' ");
        }
        //本月次年商业险已完成
        if(type.equals("cnsMonthDone")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'cns' and status = '1' ");
        }
        //本月续保交强险已完成
        if(type.equals("xbjMonthDone")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'xbj' and status = '1' ");
        }
        //本月续保商业险已完成
        if(type.equals("xbsMonthDone")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'xbs' and status = '1' ");
        }
        //本月保险活跃已完成
        if(type.equals("hytMonthDone")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'hyt' and status = '1' ");
        }
        //本月保险非活跃已完成
        if(type.equals("fhytMonthDone")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'fhyt' and status = '1' ");
        }
        //本月交强险接待已完成
        if(type.equals("jdjMonthDone")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'jdj' and status = '1' ");
        }
        //本月商业险接待已完成
        if(type.equals("jdsMonthDone")){
            sql.append("and left(expect_date,7) = '" + time + "' and type_code = 'jds' and status = '1' ");
        }
        //本月保险任务合计已完成
        if(type.equals("totalMonthDone")){
            sql.append("and left(expect_date,7) = '" + time + "' and status = '1' ");
        }
        map = this.ikpiDao.page(sql.toString(),params.toArray(),start,limit);
        return map;
    }


}
